Sumproduct Tiered % Bud Achieved Bonus Twist

Biz · Nov 14, 2019

Dear All,

I'm struggling with Sumproduct formula to calculate bonus based on percentage budget achieved (see below).
My desired amount Column U and the formula I'm trying to implement in Column T.

If % Budget achieved is 100% then you expect 7.5% Bonus, $7,500 (100,000*7.5%). If you achieved 101% then you expect a bonus higher 7.5k,
around $7,650 (see cell U9), but unfortunately can't make formula work in cell T9.

	R	S	T	U	V	W	X	Y	Z
4							Percentage of Budget Achieved	Payout %	Rate Diff %
5
6	Amount	% Achieved	Payout $	Desired Amount	Var		-	-	-
7	100,000	91%	-	750.00	750.00		90%	-	-
8	100,000	92%	689.99	1,500.00	810.01		91%	0.75%	0.75%
9	100,000	101%	7,574.93	7,650.00	75.07		92%	1.50%	0.75%
10	100,000	102%	7,649.93	7,800.00	150.07		93%	2.25%	0.75%
11	100,000	111%	9,989.90	9,150.00	- 839.90		94%	3.00%	0.75%
12	100,000	112%	10,079.90	9,300.00	- 779.90		95%	3.75%	0.75%
13	100,000	113%	10,169.90	9,450.00	- 719.90		96%	4.50%	0.75%
14	100,000	114%	10,259.90	9,600.00	- 659.90		97%	5.25%	0.75%
15	100,000	115%	10,349.90	9,750.00	- 599.90		98%	6.00%	0.75%
16	100,000	116%	10,439.90	9,900.00	- 539.90		99%	6.75%	0.75%
17	100,000	117%	10,529.90	10,050.00	- 479.90		100%	7.50%	0.75%
18	100,000	118%	10,619.90	10,200.00	- 419.90		110%	9.00%	1.50%
19	100,000	119%	10,709.90	10,350.00	- 359.90		120%	10.50%	1.50%
20	100,000	120%	10,799.89	10,500.00	- 299.89		130%	12.00%	1.50%
21	50	91%	-	0.38	0.38		140%	13.50%	1.50%
22	50	102%	3.75	3.90	0.15		150%	15.00%	1.50%

Formeln der Tabelle

Zelle	Formel
Z6	=Y6-Y5
T7	=SUMPRODUCT(--($S7>$X$6:$X$22),--($S7*(R7-$X$6:$X$22)),$Z$6:$Z$22)
V7	=U7-T7
Z7	=Y7-Y6
T8	=SUMPRODUCT(--($S8>$X$6:$X$22),--($S8*(R8-$X$6:$X$22)),$Z$6:$Z$22)
V8	=U8-T8
Z8	=Y8-Y7
T9	=SUMPRODUCT(--($S9>$X$6:$X$22),--($S9*(R9-$X$6:$X$22)),$Z$6:$Z$22)
V9	=U9-T9
Z9	=Y9-Y8
T10	=SUMPRODUCT(--($S10>$X$6:$X$22),--($S10*(R10-$X$6:$X$22)),$Z$6:$Z$22)
V10	=U10-T10
Z10	=Y10-Y9
T11	=SUMPRODUCT(--($S11>$X$6:$X$22),--($S11*(R11-$X$6:$X$22)),$Z$6:$Z$22)
V11	=U11-T11
Z11	=Y11-Y10
T12	=SUMPRODUCT(--($S12>$X$6:$X$22),--($S12*(R12-$X$6:$X$22)),$Z$6:$Z$22)
V12	=U12-T12
Z12	=Y12-Y11
T13	=SUMPRODUCT(--($S13>$X$6:$X$22),--($S13*(R13-$X$6:$X$22)),$Z$6:$Z$22)
V13	=U13-T13
Z13	=Y13-Y12
T14	=SUMPRODUCT(--($S14>$X$6:$X$22),--($S14*(R14-$X$6:$X$22)),$Z$6:$Z$22)
V14	=U14-T14
Z14	=Y14-Y13
T15	=SUMPRODUCT(--($S15>$X$6:$X$22),--($S15*(R15-$X$6:$X$22)),$Z$6:$Z$22)
V15	=U15-T15
Z15	=Y15-Y14
T16	=SUMPRODUCT(--($S16>$X$6:$X$22),--($S16*(R16-$X$6:$X$22)),$Z$6:$Z$22)
V16	=U16-T16
Z16	=Y16-Y15
T17	=SUMPRODUCT(--($S17>$X$6:$X$22),--($S17*(R17-$X$6:$X$22)),$Z$6:$Z$22)
V17	=U17-T17
Z17	=Y17-Y16
T18	=SUMPRODUCT(--($S18>$X$6:$X$22),--($S18*(R18-$X$6:$X$22)),$Z$6:$Z$22)
V18	=U18-T18
Z18	=Y18-Y17
T19	=SUMPRODUCT(--($S19>$X$6:$X$22),--($S19*(R19-$X$6:$X$22)),$Z$6:$Z$22)
V19	=U19-T19
Z19	=Y19-Y18
T20	=SUMPRODUCT(--($S20>$X$6:$X$22),--($S20*(R20-$X$6:$X$22)),$Z$6:$Z$22)
V20	=U20-T20
Z20	=Y20-Y19
T21	=SUMPRODUCT(--($S21>$X$6:$X$22),--($S21*(R21-$X$6:$X$22)),$Z$6:$Z$22)
V21	=U21-T21
Z21	=Y21-Y20
T22	=SUMPRODUCT(--($S22>$X$6:$X$22),--($S22*(R22-$X$6:$X$22)),$Z$6:$Z$22)
V22	=U22-T22
Z22	=Y22-Y21

Your help would be greatly appreciated.

Kind Regards

Biz

joeu2004 · Nov 14, 2019

I am struggling to understand the logic that you want to implement.

Please provide the manual calculations -- tier-by-tier -- that demontrate how column U is calculated.

(Do not simply emulate the behavior of the SUMPRODUCT formula.)

Ideally, provide those calculations for all tiers. But at a minimum, provide the calculations for 3 to 5 tiers so that we can see the pattern of the calculations.

joeu2004 · Nov 14, 2019

Biz said:
If % Budget achieved is 100% then you expect 7.5% Bonus, $7,500 (100,000*7.5%)

The SUMPRODUCT paradigm implements a "progressive" ("marginal") rate schedule.

So, either you do not understand how that model should work; or you have chosen the wrong model for the calculations that you want.

If $100,000 is 100% of budget, the bonus would not be simply 7.5% * $100,000.

Instead, If we interpret table in columns X and Y as a progressive rate schedule, the payout for $100,000, representing 100% of budget, would be:

0% * $90,000 for the first 90% achieved (90% tier)
plus 0.75% * $1000 for the next 1% achieved (91% tier)
plus 1.50% * $1000 for the next 1% achieved (92% tier)
plus 2.25% * $1000 for the next 1% achieved (93% tier)
[... etc ...]
plus 7.50% * $1000 for the last 1% achieved (100% tier)

But that is only $412.5.

-----

Also, just be clear on my interpretation of the data, if $100,000 represents 101% of budget achieved, you are saying that the budgeted amount is about $99,009.90, because 99.009.90*101% is about $100,000. Right?

joeu2004 · Nov 14, 2019

Sorry for the incessant responses, but I wonder if this will be helpful.

Take a look at the thread at https://www.excelforum.com/excel-ge...-formula-for-the-below-tiered-commission.html .

The details are very different. But the gist of the problem seems similar (tiered/"progressive" commission rate based on percentage of goal achieved).

Although the first response there lacks detail, if someone provides a more complete response with a turnkey solution, you might be able to apply that solution to your situation.

(OTOH, it should be essentially the manual calculations that I provided in message #3 in this thread. Forgive me if I'm "repeating" myself.)

Biz · Nov 14, 2019

Hi Joeu,

Thank you for trying to help me sort out my problem.
I'm vey happy I found a solution and happy to share with everyone.

Excel 2016 (Windows) 32 bit

	AG	AH	AI	AN
4	Lower Bound	Upper Bound	Payout %	Differential Rate Combined
5
6	0.0%	90.0%	-	=((AI6-AI5)/(AH6-AH5))-SUM($AN$5:AN5)
7	=AH6	91.0%	0.75%	=((AI7-AI6)/(AH7-AH6))-SUM($AN$5:AN6)
8	=AH7	92.0%	1.50%	=((AI8-AI7)/(AH8-AH7))-SUM($AN$5:AN7)
9	=AH8	93.0%	2.25%	=((AI9-AI8)/(AH9-AH8))-SUM($AN$5:AN8)
10	=AH9	94.0%	3.00%	=((AI10-AI9)/(AH10-AH9))-SUM($AN$5:AN9)
11	=AH10	95.0%	3.75%	=((AI11-AI10)/(AH11-AH10))-SUM($AN$5:AN10)
12	=AH11	96.0%	4.50%	=((AI12-AI11)/(AH12-AH11))-SUM($AN$5:AN11)
13	=AH12	97.0%	5.25%	=((AI13-AI12)/(AH13-AH12))-SUM($AN$5:AN12)
14	=AH13	98.0%	6.00%	=((AI14-AI13)/(AH14-AH13))-SUM($AN$5:AN13)
15	=AH14	99.0%	6.75%	=((AI15-AI14)/(AH15-AH14))-SUM($AN$5:AN14)
16	=AH15	100.0%	7.50%	=((AI16-AI15)/(AH16-AH15))-SUM($AN$5:AN15)
17	=AH16	110.0%	9.00%	=((AI17-AI16)/(AH17-AH16))-SUM($AN$5:AN16)
18	=AH17	120.0%	10.50%	=((AI18-AI17)/(AH18-AH17))-SUM($AN$5:AN17)
19	=AH18	130.0%	12.00%	=((AI19-AI18)/(AH19-AH18))-SUM($AN$5:AN18)
20	=AH19	140.0%	13.50%	=((AI20-AI19)/(AH20-AH19))-SUM($AN$5:AN19)
21	=AH20	150.00%	15.00%	=((AI21-AI20)/(AH21-AH20))-SUM($AN$5:AN20)

Sheet: % for >$1m

Excel 2016 (Windows) 32 bit

	R	S	T	U	V	W
5	Amount	% Achieved	Payout (%)	Payout $	Desired Amount	Var
6	100,000	91%	=MIN(SUMPRODUCT(--($S6>$AG$6:$AG$21),--($S6-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R6*T6	750.00	=V6-U6
7	100,000	92%	=MIN(SUMPRODUCT(--($S7>$AG$6:$AG$21),--($S7-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R7*T7	1,500.00	=V7-U7
8	100,000	101%	=MIN(SUMPRODUCT(--($S8>$AG$6:$AG$21),--($S8-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R8*T8	7,650.00	=V8-U8
9	100,000	102%	=MIN(SUMPRODUCT(--($S9>$AG$6:$AG$21),--($S9-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R9*T9	7,800.00	=V9-U9
10	100,000	111%	=MIN(SUMPRODUCT(--($S10>$AG$6:$AG$21),--($S10-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R10*T10	9,150.00	=V10-U10
11	100,000	112%	=MIN(SUMPRODUCT(--($S11>$AG$6:$AG$21),--($S11-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R11*T11	9,300.00	=V11-U11
12	100,000	113%	=MIN(SUMPRODUCT(--($S12>$AG$6:$AG$21),--($S12-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R12*T12	9,450.00	=V12-U12
13	100,000	114%	=MIN(SUMPRODUCT(--($S13>$AG$6:$AG$21),--($S13-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R13*T13	9,600.00	=V13-U13
14	100,000	115%	=MIN(SUMPRODUCT(--($S14>$AG$6:$AG$21),--($S14-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R14*T14	9,750.00	=V14-U14
15	100,000	116%	=MIN(SUMPRODUCT(--($S15>$AG$6:$AG$21),--($S15-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R15*T15	9,900.00	=V15-U15
16	100,000	117%	=MIN(SUMPRODUCT(--($S16>$AG$6:$AG$21),--($S16-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R16*T16	10,050.00	=V16-U16
17	100,000	118%	=MIN(SUMPRODUCT(--($S17>$AG$6:$AG$21),--($S17-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R17*T17	10,200.00	=V17-U17
18	100,000	119%	=MIN(SUMPRODUCT(--($S18>$AG$6:$AG$21),--($S18-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R18*T18	10,350.00	=V18-U18
19	100,000	120%	=MIN(SUMPRODUCT(--($S19>$AG$6:$AG$21),--($S19-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R19*T19	10,500.00	=V19-U19
20	100,000	160%	=MIN(SUMPRODUCT(--($S20>$AG$6:$AG$21),--($S20-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R20*T20	15,000.00	=V20-U20
21	50	91%	=MIN(SUMPRODUCT(--($S21>$AG$6:$AG$21),--($S21-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R21*T21	0.38	=V21-U21
22	50	91%	=MIN(SUMPRODUCT(--($S22>$AG$6:$AG$21),--($S22-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R22*T22	0.38	=V22-U22
23	50	102%	=MIN(SUMPRODUCT(--($S23>$AG$6:$AG$21),--($S23-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)	=R23*T23	3.90	=V23-U23

Sheet: % for >$1m

If anyone has any alternative solutions or suggestion please share.

Biz

Biz · Nov 14, 2019

Please note, cell AA22 is the max formula to derive the maximum Payout% of 15%.

Sumproduct Tiered % Bud Achieved Bonus Twist

Biz

Well-known Member

Excel Facts

joeu2004

Banned user

joeu2004

Banned user

joeu2004

Banned user

Biz

Well-known Member

Biz

Well-known Member

Similar threads

Forum statistics

Share this page

Sumproduct Tiered % Bud Achieved Bonus Twist

Biz

Well-known Member

Excel Facts

joeu2004

Banned user

joeu2004

Banned user

joeu2004

Banned user

Biz

Well-known Member

Biz

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock