Allocating amounts to various bands/tiers

[jasonkgreen]

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.

	A	B	C	D	E	F	G	H	I	J	K
1	Target Bookings	$25,000,000
2	Role	AE
3
4		Band 1	Band 2	Band 3	Band 4	Band 5
5	Band Cap	$20,000,000​	$42,000,000​	$50,000,000​	$55,000,000​	$200,000,000​
6	Band Allocation	20,000,000​	5,000,000​	​	​	​

 

[MountainFog]

You can use an if statement in column B:

=IF($B$1>B$5, B$5, $B$1)

Columns C and then drag across to any number of bands:

=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

 

[Dave Patton]

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?

 

[jasonkgreen]

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.

 

[Dave Patton]

"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?

 

[jasonkgreen]

"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.

 

[Dave Patton]

You did not answer the questions.

T202311a.xlsm
	A	B	C
1	Target Bookings	$1,000,000,000	$5,272,000
2
8
9	Bracket	Rate	Rate Differential
10	0	0.10%	0.10%
11	20,000,000	0.20%	0.10%
12	62,000,000	0.30%	0.10%
13	112,000,000	0.40%	0.10%
14	167,000,000	0.50%	0.10%
15	367,000,000	0.60%	0.10%
16	1E+308
5a

Cell Formulas
Range	Formula
C1	C1	=SUM((B1>rB)*(B1-rB)*rR)

Named Ranges
Name	Refers To	Cells
'5a'!rB	='5a'!$A$10:$A$15	C1
rR	='5a'!$C$10:$C$15	C1

 

[bebo021999]

Is it are you looking for?

Clocking.xlsx
	A	B	C	D	E	F
1	Target Bookings	25,000,000
2	Role	AE
3
4		Band 1	Band 2	Band 3	Band 4	Band 5
5	Band Cap	20,000,000	42,000,000	50,000,000	55,000,000	200,000,000
6	Band Allocation	20,000,000	5,000,000	-	-	-
Sheet2

Cell Formulas
Range	Formula
B6:F6	B6	=MIN($B$1-SUM($A$6:A6),B$5)

 

[Dave Patton]

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
	A	B	C	D
1	Target Bookings	$1,000,000,000	$5,272,000	$5,272,000
5a

Cell Formulas
Range	Formula
C1	C1	=SUM((B1>rB)*(B1-rB)*rR)
D1	D1	=Commission_Bookings(B1)

Named Ranges
Name	Refers To	Cells
'5a'!rB	='5a'!$A$11:$A$16	C1
'5a'!rR	='5a'!$C$11:$C$16	C1

Lambda Functions
Name	Formula
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)))