Allocating amounts to various bands/tiers

jasonkgreen

Board Regular
Joined
Feb 21, 2013
Messages
50
Below are the caps for each band/tier. Band 1 has a $20M cap. Any sales over $20M goes into Band 2. So if I hit $25M in sales, $20M goes in Band 1 and $5M goes in Band 2. How can I use a formula to accomplish this? I want any bands that are not reached to be blank.


ABCDEFGHIJK
1Target Bookings$25,000,000
2RoleAE
3
4Band 1Band 2Band 3Band 4Band 5
5Band Cap
$20,000,000​
$42,000,000​
$50,000,000​
$55,000,000​
$200,000,000​
6Band Allocation
20,000,000​
5,000,000​
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
You can use an if statement in column B:

Excel Formula:
=IF($B$1>B$5, B$5, $B$1)

Columns C and then drag across to any number of bands:
Excel Formula:
=IF(IF($B$1>SUM($B$5:C$5),C$5,$B$1 - SUM(B$5:B$5))>0,IF($B$1>SUM($B$5:C$5),C$5,$B$1 - SUM($B$5:B$5)),0)

Note this will only work up to the largest band cap. If you are at more than $367,000,000.00 in the above example, you need to create new bands as it will cap it at $367,000,000.00
 
Last edited:
Upvote 0
Why do you want to calculate the amount for each band?
Do you calculate something based on the amounts in the bands?
What version of Excel are you using?
N.B. You can post an extract of your sheet with the forum's tool named XL2BB.
For 1 B, what would be the amounts per band and then what would be the ultimate calculation?
 
Upvote 0
Why do you want to calculate the amount for each band?
Do you calculate something based on the amounts in the bands?
What version of Excel are you using?
N.B. You can post an extract of your sheet with the forum's tool named XL2BB.
For 1 B, what would be the amounts per band and then what would be the ultimate calculation?
I'm calculating a commission for each band. I'm using Excel 365. I'm not sure I understand the last question.
 
Upvote 0
"I'm calculating a commission for each band. I'm using Excel 365. I'm not sure I understand the last question."

Why don't you provide full information and just calculate the commission?
What would be the commission on 1 billion or 400,000,000?
 
Upvote 0
"I'm calculating a commission for each band. I'm using Excel 365. I'm not sure I understand the last question."

Why don't you provide full information and just calculate the commission?
What would be the commission on 1 billion or 400,000,000?
I'm setting this up so a manager can change the revenue target and get an answer immediately. Each band has a different rate. I tried the XL2BB and I can't get the plugin to work.
 
Upvote 0
You did not answer the questions.

T202311a.xlsm
ABC
1Target Bookings$1,000,000,000$5,272,000
2
8
9BracketRateRate Differential
1000.10%0.10%
1120,000,0000.20%0.10%
1262,000,0000.30%0.10%
13112,000,0000.40%0.10%
14167,000,0000.50%0.10%
15367,000,0000.60%0.10%
161E+308
5a
Cell Formulas
RangeFormula
C1C1=SUM((B1>rB)*(B1-rB)*rR)
Named Ranges
NameRefers ToCells
'5a'!rB='5a'!$A$10:$A$15C1
rR='5a'!$C$10:$C$15C1
 
Upvote 0
Is it are you looking for?

Clocking.xlsx
ABCDEF
1Target Bookings25,000,000
2RoleAE
3
4Band 1Band 2Band 3Band 4Band 5
5Band Cap20,000,00042,000,00050,000,00055,000,000200,000,000
6Band Allocation20,000,0005,000,000---
Sheet2
Cell Formulas
RangeFormula
B6:F6B6=MIN($B$1-SUM($A$6:A6),B$5)
 
Upvote 0
The commission for 1 billion.
I posted the result but the OP just needed the calculation for the bands.
I guessed at the rates and prepared an example of the new function that could be used.
The new XL2BB will show the information.

T202311a.xlsm
ABCD
1Target Bookings$1,000,000,000$5,272,000$5,272,000
5a
Cell Formulas
RangeFormula
C1C1=SUM((B1>rB)*(B1-rB)*rR)
D1D1=Commission_Bookings(B1)
Named Ranges
NameRefers ToCells
'5a'!rB='5a'!$A$11:$A$16C1
'5a'!rR='5a'!$C$11:$C$16C1
Lambda Functions
NameFormula
Commission_Bookings=LAMBDA(Target_Bookings, LET(x,Target_Bookings,aB,{0;20000000;62000000;112000000;167000000;367000000},aR,{0.001;0.001;0.001;0.001;0.001;0.001},SUM((x>aB)*(x-aB)*aR)))
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top