Sumproduct Tiered % Bud Achieved Bonus Twist

[Biz]

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
Amount	% Achieved	Payout $	Desired Amount	Var

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: #FF9933"]Percentage of Budget Achieved[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]Payout %[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]Rate Diff %[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="align: right"]- [/TD]
[TD="align: right"]- [/TD]
[TD="align: right"]- [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]91%[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 750.00 [/TD]
[TD="align: right"] 750.00 [/TD]

[TD="align: right"]90% [/TD]
[TD="align: right"]- [/TD]
[TD="align: right"]- [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"] 689.99 [/TD]
[TD="align: right"] 1,500.00 [/TD]
[TD="align: right"] 810.01 [/TD]

[TD="align: right"]91% [/TD]
[TD="align: right"]0.75% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]101%[/TD]
[TD="align: right"] 7,574.93 [/TD]
[TD="align: right"] 7,650.00 [/TD]
[TD="align: right"] 75.07 [/TD]

[TD="align: right"]92% [/TD]
[TD="align: right"]1.50% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]102%[/TD]
[TD="align: right"] 7,649.93 [/TD]
[TD="align: right"] 7,800.00 [/TD]
[TD="align: right"] 150.07 [/TD]

[TD="align: right"]93% [/TD]
[TD="align: right"]2.25% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]111%[/TD]
[TD="align: right"] 9,989.90 [/TD]
[TD="align: right"] 9,150.00 [/TD]
[TD="align: right"]- 839.90 [/TD]

[TD="align: right"]94% [/TD]
[TD="align: right"]3.00% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]112%[/TD]
[TD="align: right"] 10,079.90 [/TD]
[TD="align: right"] 9,300.00 [/TD]
[TD="align: right"]- 779.90 [/TD]

[TD="align: right"]95% [/TD]
[TD="align: right"]3.75% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]113%[/TD]
[TD="align: right"] 10,169.90 [/TD]
[TD="align: right"] 9,450.00 [/TD]
[TD="align: right"]- 719.90 [/TD]

[TD="align: right"]96% [/TD]
[TD="align: right"]4.50% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]114%[/TD]
[TD="align: right"] 10,259.90 [/TD]
[TD="align: right"] 9,600.00 [/TD]
[TD="align: right"]- 659.90 [/TD]

[TD="align: right"]97% [/TD]
[TD="align: right"]5.25% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]115%[/TD]
[TD="align: right"] 10,349.90 [/TD]
[TD="align: right"] 9,750.00 [/TD]
[TD="align: right"]- 599.90 [/TD]

[TD="align: right"]98% [/TD]
[TD="align: right"]6.00% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]116%[/TD]
[TD="align: right"] 10,439.90 [/TD]
[TD="align: right"] 9,900.00 [/TD]
[TD="align: right"]- 539.90 [/TD]

[TD="align: right"]99% [/TD]
[TD="align: right"]6.75% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]117%[/TD]
[TD="align: right"] 10,529.90 [/TD]
[TD="align: right"] 10,050.00 [/TD]
[TD="align: right"]- 479.90 [/TD]

[TD="align: right"]100% [/TD]
[TD="align: right"]7.50% [/TD]
[TD="align: right"]0.75% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]118%[/TD]
[TD="align: right"] 10,619.90 [/TD]
[TD="align: right"] 10,200.00 [/TD]
[TD="align: right"]- 419.90 [/TD]

[TD="align: right"]110% [/TD]
[TD="align: right"]9.00% [/TD]
[TD="align: right"]1.50% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]119%[/TD]
[TD="align: right"] 10,709.90 [/TD]
[TD="align: right"] 10,350.00 [/TD]
[TD="align: right"]- 359.90 [/TD]

[TD="align: right"]120% [/TD]
[TD="align: right"]10.50% [/TD]
[TD="align: right"]1.50% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"]120%[/TD]
[TD="align: right"] 10,799.89 [/TD]
[TD="align: right"] 10,500.00 [/TD]
[TD="align: right"]- 299.89 [/TD]

[TD="align: right"]130% [/TD]
[TD="align: right"]12.00% [/TD]
[TD="align: right"]1.50% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]21[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]91%[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 0.38 [/TD]
[TD="align: right"] 0.38 [/TD]

[TD="align: right"]140% [/TD]
[TD="align: right"]13.50% [/TD]
[TD="align: right"]1.50% [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]22[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]102%[/TD]
[TD="align: right"] 3.75 [/TD]
[TD="align: right"] 3.90 [/TD]
[TD="align: right"] 0.15 [/TD]

[TD="align: right"]150% [/TD]
[TD="align: right"]15.00% [/TD]
[TD="align: right"]1.50% [/TD]

</tbody>

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 <tbody> </tbody>

<tbody>
</tbody>

Your help would be greatly appreciated.

Kind Regards

Biz

 

[joeu2004]

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]

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]

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]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

AG​

[/th][th]

AH​

[/th][th]

AI​

[/th][th]

AN​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#FFFFCC]

Lower Bound​

[/td][td=bgcolor:#FFFFCC]

Upper Bound​

[/td][td=bgcolor:#FFFFCC]

Payout %​

[/td][td=bgcolor:#FFFFCC]

Differential Rate Combined​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

0.0%​

[/td][td]

90.0%​

[/td][td]

-​

[/td][td]

=((AI6-AI5)/(AH6-AH5))-SUM($AN$5:AN5)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

=AH6​

[/td][td]

91.0%​

[/td][td]

0.75%​

[/td][td]

=((AI7-AI6)/(AH7-AH6))-SUM($AN$5:AN6)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

=AH7​

[/td][td]

92.0%​

[/td][td]

1.50%​

[/td][td]

=((AI8-AI7)/(AH8-AH7))-SUM($AN$5:AN7)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

=AH8​

[/td][td]

93.0%​

[/td][td]

2.25%​

[/td][td]

=((AI9-AI8)/(AH9-AH8))-SUM($AN$5:AN8)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

=AH9​

[/td][td]

94.0%​

[/td][td]

3.00%​

[/td][td]

=((AI10-AI9)/(AH10-AH9))-SUM($AN$5:AN9)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

=AH10​

[/td][td]

95.0%​

[/td][td]

3.75%​

[/td][td]

=((AI11-AI10)/(AH11-AH10))-SUM($AN$5:AN10)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

=AH11​

[/td][td]

96.0%​

[/td][td]

4.50%​

[/td][td]

=((AI12-AI11)/(AH12-AH11))-SUM($AN$5:AN11)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

=AH12​

[/td][td]

97.0%​

[/td][td]

5.25%​

[/td][td]

=((AI13-AI12)/(AH13-AH12))-SUM($AN$5:AN12)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

=AH13​

[/td][td]

98.0%​

[/td][td]

6.00%​

[/td][td]

=((AI14-AI13)/(AH14-AH13))-SUM($AN$5:AN13)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

=AH14​

[/td][td]

99.0%​

[/td][td]

6.75%​

[/td][td]

=((AI15-AI14)/(AH15-AH14))-SUM($AN$5:AN14)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

=AH15​

[/td][td]

100.0%​

[/td][td]

7.50%​

[/td][td]

=((AI16-AI15)/(AH16-AH15))-SUM($AN$5:AN15)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

=AH16​

[/td][td]

110.0%​

[/td][td]

9.00%​

[/td][td]

=((AI17-AI16)/(AH17-AH16))-SUM($AN$5:AN16)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

=AH17​

[/td][td]

120.0%​

[/td][td]

10.50%​

[/td][td]

=((AI18-AI17)/(AH18-AH17))-SUM($AN$5:AN17)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

=AH18​

[/td][td]

130.0%​

[/td][td]

12.00%​

[/td][td]

=((AI19-AI18)/(AH19-AH18))-SUM($AN$5:AN18)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

=AH19​

[/td][td]

140.0%​

[/td][td]

13.50%​

[/td][td]

=((AI20-AI19)/(AH20-AH19))-SUM($AN$5:AN19)​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]

=AH20​

[/td][td]

150.00%​

[/td][td]

15.00%​

[/td][td]

=((AI21-AI20)/(AH21-AH20))-SUM($AN$5:AN20)​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: % for >$1m[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

R​

[/th][th]

S​

[/th][th]

T​

[/th][th]

U​

[/th][th]

V​

[/th][th]

W​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Amount[/td][td]% Achieved[/td][td]Payout (%)[/td][td]Payout $[/td][td]Desired Amount[/td][td]Var[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

100,000​

[/td][td]

91%​

[/td][td]

=MIN(SUMPRODUCT(--($S6>$AG$6:$AG$21),--($S6-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R6*T6​

[/td][td]

750.00​

[/td][td]

=V6-U6​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

100,000​

[/td][td]

92%​

[/td][td]

=MIN(SUMPRODUCT(--($S7>$AG$6:$AG$21),--($S7-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R7*T7​

[/td][td]

1,500.00​

[/td][td]

=V7-U7​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

100,000​

[/td][td]

101%​

[/td][td]

=MIN(SUMPRODUCT(--($S8>$AG$6:$AG$21),--($S8-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R8*T8​

[/td][td]

7,650.00​

[/td][td]

=V8-U8​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

100,000​

[/td][td]

102%​

[/td][td]

=MIN(SUMPRODUCT(--($S9>$AG$6:$AG$21),--($S9-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R9*T9​

[/td][td]

7,800.00​

[/td][td]

=V9-U9​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

100,000​

[/td][td]

111%​

[/td][td]

=MIN(SUMPRODUCT(--($S10>$AG$6:$AG$21),--($S10-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R10*T10​

[/td][td]

9,150.00​

[/td][td]

=V10-U10​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

100,000​

[/td][td]

112%​

[/td][td]

=MIN(SUMPRODUCT(--($S11>$AG$6:$AG$21),--($S11-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R11*T11​

[/td][td]

9,300.00​

[/td][td]

=V11-U11​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

100,000​

[/td][td]

113%​

[/td][td]

=MIN(SUMPRODUCT(--($S12>$AG$6:$AG$21),--($S12-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R12*T12​

[/td][td]

9,450.00​

[/td][td]

=V12-U12​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

100,000​

[/td][td]

114%​

[/td][td]

=MIN(SUMPRODUCT(--($S13>$AG$6:$AG$21),--($S13-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R13*T13​

[/td][td]

9,600.00​

[/td][td]

=V13-U13​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

100,000​

[/td][td]

115%​

[/td][td]

=MIN(SUMPRODUCT(--($S14>$AG$6:$AG$21),--($S14-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R14*T14​

[/td][td]

9,750.00​

[/td][td]

=V14-U14​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

100,000​

[/td][td]

116%​

[/td][td]

=MIN(SUMPRODUCT(--($S15>$AG$6:$AG$21),--($S15-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R15*T15​

[/td][td]

9,900.00​

[/td][td]

=V15-U15​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

100,000​

[/td][td]

117%​

[/td][td]

=MIN(SUMPRODUCT(--($S16>$AG$6:$AG$21),--($S16-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R16*T16​

[/td][td]

10,050.00​

[/td][td]

=V16-U16​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

100,000​

[/td][td]

118%​

[/td][td]

=MIN(SUMPRODUCT(--($S17>$AG$6:$AG$21),--($S17-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R17*T17​

[/td][td]

10,200.00​

[/td][td]

=V17-U17​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

100,000​

[/td][td]

119%​

[/td][td]

=MIN(SUMPRODUCT(--($S18>$AG$6:$AG$21),--($S18-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R18*T18​

[/td][td]

10,350.00​

[/td][td]

=V18-U18​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

100,000​

[/td][td]

120%​

[/td][td]

=MIN(SUMPRODUCT(--($S19>$AG$6:$AG$21),--($S19-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R19*T19​

[/td][td]

10,500.00​

[/td][td]

=V19-U19​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

100,000​

[/td][td]

160%​

[/td][td]

=MIN(SUMPRODUCT(--($S20>$AG$6:$AG$21),--($S20-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R20*T20​

[/td][td]

15,000.00​

[/td][td]

=V20-U20​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]

50​

[/td][td]

91%​

[/td][td]

=MIN(SUMPRODUCT(--($S21>$AG$6:$AG$21),--($S21-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R21*T21​

[/td][td]

0.38​

[/td][td]

=V21-U21​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td]

50​

[/td][td]

91%​

[/td][td]

=MIN(SUMPRODUCT(--($S22>$AG$6:$AG$21),--($S22-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R22*T22​

[/td][td]

0.38​

[/td][td]

=V22-U22​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td]

50​

[/td][td]

102%​

[/td][td]

=MIN(SUMPRODUCT(--($S23>$AG$6:$AG$21),--($S23-$AG$6:$AG$21),$AN$6:$AN$21),$AA$22)​

[/td][td]

=R23*T23​

[/td][td]

3.90​

[/td][td]

=V23-U23​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: % for >$1m[/td][/tr][/table]

If anyone has any alternative solutions or suggestion please share.

Biz

 

[Biz]

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