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.
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.
Explaining calculations
If $65 000 is used then the formula returns $2 925, here is how that number is calculated: 65 000 * 4.5% = 2 925
| Amount | Percentage | Result |
| 65 000 | 4.5% | 2 925 |
| Total | 2 925 |
$125 000 returns $5 000. 100 000 * 4.5% = 4 500. 25 000 * 2% = 500. 4 500 + 500 = 5000.
| Amount | Percentage | Result |
| 100 000 | 4.5% | 4 500 |
| 25 000 | 2% | 500 |
| Total | 5 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
| Amount | Percentage | Result |
| 100 000 | 4.5% | 4 500 |
| 150 000 | 2% | 3 000 |
| 30 000 | 1.2% | 360 |
| Total | 7 860 |
$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.
| 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 |
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.
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 lets you determine 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.
Final thoughts
The formula can be simplified to:
but that formula is harder for me to explain.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
SUMPRODUCT – multiple criteria
The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to […]
How to use the SUMPRODUCT function
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
SUMPRODUCT and nested IF functions
I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]
The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3. […]
You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]
18 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!