How to do tiered calculations in one formula
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This kind of formula is often used to calculate commissions, bonuses, pricing, fees or charges, discounts, volume pricing, volume rebate, and performance incentives.
The table shows the different thresholds or levels that a specific percentage applies to, if an amount is larger than the first level then multiple calculations are necessary in order to calculate the total. The formula takes care of these additional calculations as well, no need for helper columns.
I have written an article about how to Use price ranges to determine discounts, it also explains how to calculate linear discounts.
Table of contents
1. Formula
Formula in cell C10:
There are four different values B10:B13 in order to demonstrate how the total changes based on the percentage and levels used, I will explain below how each value is calculated.
2. Explaining calculations
2.1 Example 1 - $65 000
If $65 000 is used then the formula returns $2 925, here is how that number is calculated: 65 000 * 4.5% = 2 925
The amount is in cell B10 and the calculation is in cell C10, see the image above.
| Amount | Percentage | Result |
| 65 000 | 4.5% | 2 925 |
| Total | 2 925 |
2.2 Example 2 - $125 000
$125 000 returns $5 000. 100 000 * 4.5% = 4 500. 25 000 * 2% = 500. 4 500 + 500 = 5 000.
The amount is in cell B11 and the calculation is in cell C11, see the image above.
| Amount | Percentage | Result |
| 100 000 | 4.5% | 4 500 |
| 25 000 | 2% | 500 |
| Total | 5 000 |
2.3 Example 3 - $280 000
$280 000 returns $7 860. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 30 000 * 1.2% = 360. 4 500 + 3 000 + 360 = 7 860
The amount is in cell B12 and the calculation is in cell C12, see the image above.
| Amount | Percentage | Result |
| 100 000 | 4.5% | 4 500 |
| 150 000 | 2% | 3 000 |
| 30 000 | 1.2% | 360 |
| Total | 7 860 |
2.4 Example 4 - $540 000
$540 000 returns $10 580. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 250 000 * 1.2% = 3 000. 40 000 * 0.2% = 80. 4 500 + 3 000 + 3 000 + 80 = 10 580.
The amount is in cell B13 and the calculation is in cell C13, see the image above.
| Amount | Percentage | Result |
| 100 000 | 4.5% | 4 500 |
| 150 000 | 2% | 3 000 |
| 250 000 | 1.2% | 3 000 |
| 40 000 | 0.2% | 80 |
| Total | 10 580 |
3. Explaining formula in cell C10
The formula contains two SUMPRODUCT functions, the first one calculates the result based on the amount that is above a specific level and the corresponding percentage.
The second SUMPRODUCT function calculates tiered values based on the amounts up to the reached level and their corresponding percentages. The formula then adds those numbers and returns the total.
Recommended article: How to return a value if lookup value is in a range
Step 1 - First SUMPRODUCT function
The first two logical expressions determine which tier the amount in cell B10 reaches.
(B10<=$C$4:$C$7)* (B10>$B$4:$B$7)
becomes
(65000<={100000; 250000; 500000; 999999})*(65000>{0; 100000; 250000; 500000})
becomes
({TRUE; TRUE; TRUE; TRUE})*({TRUE; FALSE; FALSE; FALSE})
and returns {1; 0; 0; 0}.
Step 2 - Subtract value with tier levels
The parentheses let you control the order of operation, we want to subtract before we multiply the arrays in order to get the correct result we are looking for.
(B10- $B$4:$B$7)
becomes
(65000 - {0; 100000; 250000; 500000})
and returns {65000; -35000; -185000; -435000}.
Step 3 - Multiply arrays
(B10<$C$4:$C$7)*(B10>$B$4:$B$7)*(B10- $B$4:$B$7)* $D$4:$D$7
becomes
{1; 0; 0; 0}*{65000; -35000; -185000; -435000}*{0.045;0.02;0.012;0.002}
and returns {2925; 0; 0; 0}.
Step 4 - Add values
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)
becomes
SUMPRODUCT({2925;0;0;0})
and returns 2925.
Step 5 - Second SUMPRODUCT function
((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7
becomes
(65000>{100000;250000;500000;999999})*({100000;150000;250000;499999})*{0.045;0.02;0.012;0.002}
and returns {0;0;0;0}.
Step 6 - Add values in array
SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)
becomes
SUMPRODUCT({0;0;0;0})
and returns 0.
Step 7 - Add numbers
SUMPRODUCT((B10<=$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)+ SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)
becomes
2925 + 0
and returns 2925 in cell C10.
4. Final thoughts
The formula can be simplified to:
but that formula is harder for me to explain.
Excel 365 users can use this smaller dynamic array formula:
I recommend reading how to use VLOOKUP to calculate discounts, commissions, tariffs, charges, shipping costs, packaging expenses, or bonuses
5. Excel file
Find numbers in sum category
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Sum category
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]
This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
To extract groups from cell range B3:B10 I use the following regular formula in cell B13.
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]
The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]
The easiest way to sum a cell range is to simply select the cell range and read the values in […]
This article explains why your formula is not working properly, there are usually four different things that can go wrong. […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
26 Responses to “How to do tiered calculations in one formula”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thanks. This was super helpful.
What if my value in B10 is 100000. I am getting result as 0
Indresh,
You found an error in my formula. I have updated the formula and the uploaded file.
Thank you.
Why is it necessary to add the second sumproduct formula if you are reaching the same number with just the first formula?
THANKS, This is quite useful.
By any chance do you have banded interest rate excel
Say band 1 is from 0-5000 interest rate is 6% and Band 2 is 5001 to 12,000, INTEREST RATE IS 10%
If my balance is 6000 then interest to be applied is 10%
if my balance is 2000 then interest to be applied is 6%
TIA
Indresh Misha
Check out this article: https://www.get-digital-help.com/return-value-if-in-range-in-excel/
Brilliant, thanks a lot for all your help.
Oscar: You are a lifesaver. Thank you very much for this fantastic walkthrough!
You are welcome!
Hello,
What if I am looking to calculate partial payouts? Ex. rebates are earned in bands, but only for the amount in each band.
Say that we are looking to reward spending growth above a baseline.
The baseline is $100,000.
We create three achievement bands:
100% - 105%,
105% - 115%,
and greater than 115%.
Therefore all excess dollars between 100,000 and 105,000 will receive a 5% rebate, additional dollars between 105,000 and 115,000 will receive a 10% rebate and additional dollars above 115,000 will receive a 15% rebate:
example, if spend is: $125,000
Growth equals $25,000
First 5% of dollars greater than $100,000 earn 5%, so $5,000 * 0.05 = $250
Next band between $105,000 and $115,000 earns 10% so $10,000 * 0.10 = $1000
Finally, dollars greater than $115,000 earn 15% so $125,000 - $115,000 = $10,000 and $10,000 * 0.15 = $1500
Total rebate = $1,500 + $1000 + $250 = $2,750
Aaron,
I used your numbers and got this:
Hi Oscar,
This was incredibly helpful!! Thank you.
I was wondering if you know DAX coding in Power BI? I am currently trying to transfer this formula over to DAX, but I am having a challenging time doing so.
Thought I would ask!
Thanks!
Ollie Musekamp
No, can't help you out.
Hi Guys,
Can anyone assist in the formula calculation in general and specifically in excel for the below please: Will really appreciate it. Thanks
47500 is the settled/base value; and different percentage needs to be calculated between the ranges as below.
QUERY:
Settled Amount 47500
VALUE?
On all settlements upto 2,500.00 25% ?
On all excess over 2,500.00 upto 5,000.00 15% ?
On all excess over 5,000.00 upto 10,000.00 7.5% ?
On all excess over 10,000.00 upto 20,000.00 5% ?
On the excess over 20,000.00 2.5% ?
TOTAL: =SUM(ABOVE)
Shyne,
I extended the cell references to include an additonal row.
Hi, thanks very much for this it was very useful! Could I please ask if you know of a way of calculating when there are several different tiers. For example (Column D is Tier 1, E is Tier 2 etc). How would you update the formula to look at the percentages in column E instead of D automatically? Thank you
This literally saved my life, thank you so much! I was searching long and hard for something like this to calculate progressive fees.
Though I have a Question:
- Is there a way to use this with something like vlookup (or some other function)? I have several tables in which each table has a different 'From', 'To' and '%'.
Example. I have a data validation drop down list of the different categories of fees and I would like it that when you select 'X' cateogory from the data validation options, it then looks at the correct table and calculates the correct fees?
I've been trying to figure a way to do this but no luck so far.
Cheers,
Lee
I've been trying to work something like this out for years. Thanks so much!
I just wanted to say thank you very much for this.
I have spent in total the last 4 hours or so trying to figure out how to make this all work. I've multiple websites and used vlookup and if formulas. I love how well each step is explained on this website and that you have actually taken the time to explain each part of the formula and what it is doing.
Regards,
A very appreciative human being
Kristy
Thank you for your kind words.
Awesome. Exactly what I was looking for. I appreciate the explanation too.
Quite helpful! I had one that worked to determine my electric bill for any usage, but it was all spread out with hidden rows and columns. This one is more succinct and shareable.
Did take me a while to digest all this, as the only info I really needed - the Final Thoughts formula - was at the end and might be better up front before showing how it's built (as well as how to adjust add rows).
Thank you. This was very helpful. Is there a way that I could also get this down to a daily figure? This would be great in refiguring interest on a tiered rate account but I need to get to a daily figure. Could /365 be added somewhere without messing up the formula?
Rhonda, thank you for commenting. Is this what you are looking for?
=(SUMPRODUCT((B10<=$C$4:$C$7)*(B10>$B$4:$B$7)*(B10-$B$4:$B$7)*$D$4:$D$7)+SUMPRODUCT(((B10>$C$4:$C$7)*($C$4:$C$7-$B$4:$B$7))*$D$4:$D$7))/365
Hello,
Your formula is going to be a huge help, however, I am having an issue making it work! I've downloaded the sheet and recreated it myself with the same results.
Tier 1 0.00-60.00 @ $0.419 Per
Tier 2 61.00- 120.00 @ $0.7340
Tier 3 121.00 - 999999 @ $1.103
Value 108 should return at total of $60.37 but its returning $59.64
Tier 1 is calculating correctly at $25.14 however tier 2 is calculating at $34.498 and it should be $35.23.
I've tried other values 122 and 1 and both are incorrect as well.
What am I doing wrong!?
Charlene Whitely,
Your second and third tier range are wrong.
Tier 1 0.00-60.00
Tier 2 60.00- 120.00
Tier 3 120.00 - 999999
This returns 60.37 in my worksheet.