Need to calculate tiered commissions based on YTD volume

[amccoy]

Hey everyone,

I've searched high and low for the best way (or even the worst way) to calculate this. Here's what I am trying to accomplish:

I need to use YTD volumes ($) to determine what rate (%) should be used. I've tried SUMPRODUCT and various nested IF statements, but as soon as I get to a transaction that falls between two tiers, the formula fails.

Here are the tiers:

The min/max ranges are based on YTD volumes (also a running tally in the sheet). I want the formula to look at the running tally, and determine what rate % to apply to another column (commissions).

As I mentioned, I seem to be having the most trouble is when a transaction falls between two tiers. For example, a transaction brings the YTD total to $7,744,606. I need a portion allocated to the 30% rate, and the rest at the new 25% rate. I can get a few approaches to work when a transaction falls entirely in a tier.

Unfortunately, I am helping a friend and this is her personal transaction/comissions so I can't attach the sheet - but hopefully I've been concise enough that you can point me in the right direction.

Thanks!

 

[Legacy 143009]

Are you looking something like this:

=VLOOKUP(7744606,$A$2:$C$6,3,1)

EDIT: After seeing @AhoyNC 's answer, I understand that my approach is completely wrong.

 

[AhoyNC]

See if this works:

Book1
	A	B	C	D
1	Min	Max	%	% Dif
2	$ -	$ 7,500,000	30%
3	$ 7,500,001	$ 10,000,000	25%	-5%
4	$ 10,000,001	$ 15,000,000	20%	-5%
5	$ 15,000,001	$ 20,000,000	15%	-5%
6	$ 20,000,001	$ 10,000,000	10%	-5%
7
8	Amount YTD	$ 7,744,606
9
10	Comission	$ 2,311,151.55
Sheet2

Cell Formulas
Range	Formula
D3:D6	D3	=C3-C2
B10	B10	=SUMPRODUCT(--(B8>$A$2:$A$6),(B8-$A$2:$A$6),{0.3;-0.05;-0.05;-0.05;-0.05})

 

[Dave Patton]

Since you have 365, try the following C10

Commission2022.xlsm
	A	B	C	D
1	Brackets	%	Rate Dif	Arithmetic
2	0	30%	30%	2,250,000.00
3	7,500,000	25%	-5%	61,151.50
4	10,000,000	20%	-5%	0.00
5	15,000,000	15%	-5%	0.00
6	20,000,000	10%	-5%	0.00
7
8	Amount YTD	7,744,606.00
9
10	Commission	2,311,151.50	2,311,151.50	2,311,151.50
11
10a

Cell Formulas
Range	Formula
C2:C6	C2	=B2-N(B1)
D2:D6	D2	=MAX(0,MIN(A3-A2,$B$8-A2)*B2)
B10	B10	=SUMPRODUCT(--(B8>$A$2:$A$6),(B8-$A$2:$A$6),C2:C6)
C10	C10	=SUM((B8>A2:A6)*(B8-A2:A6)*C2:C6)
D10	D10	=SUM(D2:D6)

 

[Dave Patton]

With D11, the Bracket and Rate Difference arrays are named. Use Formulas Name Manager and name the arrays.
For aR, select C11 and highlight B3:B7-B2:B6 and press F9; copy the array and move to Name Manager.

Commission2022.xlsm
	A	B	C	D
1	Brackets	%
2	B2 is blank
3	0	30%
4	7,500,000	25%
5	10,000,000	20%
6	15,000,000	15%
7	20,000,000	10%
8
9	Amount YTD	7,744,606.00
10
11	Commission	2,311,151.50	2,311,151.50	2,311,151.50
12
10aa

Cell Formulas
Range	Formula
B11	B11	=SUMPRODUCT(--(B9>$A$3:$A$7),(B9-$A$3:$A$7),B3:B7-B2:B6)
C11	C11	=SUM((B9>A3:A7)*(B9-A3:A7)*(B3:B7-B2:B6))
D11	D11	=SUM((B9>aB)*(B9-aB)*aR)