Formula to calculate tiered pricing based on volume

Topic Labels: Formulas
4429 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I’m brand new to Airtable and am loving it so far. I have a product which has tiered pricing based on the volume of licenses that are purchased. Example 1-4 = $75, 5-10= $50, 11-24 = $55. There are 14 tiers. I want to enter the exact number of licenses that are being purchased, identify the category that belongs to, display the per price cost and then calculate the total license fees for # Licenses * Cost per License. Any help is appreciated.

I also have other types of fees which I can manually enter as they are simple, but I’d also like to add the license fees plus the other fees to give me the total amount to be billed. Possible?

2 Replies 2

What Tables and Fields do you have? I think you need to create a Tiers table, and relate every Order to a Tier.

I’m not sure that a Tiers table would necessarily make things easier. Figuring out the cost per license can be done with a series of nested IF statements. Here’s a quick example based on the three-tier sample provided, assuming there’s a Count field where you enter the number of licenses (and I corrected what looks like an error: you have the 5-10 tier with a lower price than the 11-24 tier). I also used $45 as the default for anything 25 or higher:

IF(Count < 5, 75, IF(Count < 11, 55, IF(Count < 25, 50, 45)))

I named this field “$ per License”. Just continue adding nested IF statements for your other tiers.

From there, make a formula field named Total License Fees with this formula:

Count * {$ per License}

Here’s what my sample looks like: