Bonus payout calculator based on units sold not percentage of cash

[snakel87]

Mr. Excel Forum,

I have a sales team that gets paid based on units sold not dollars sold and then a percentage for bonus. I'm trying to figure out a formula that would allow them to track sales and see potential commissions.
For basic math sake they need to sell 60 units a quarter.
They only get paid if they hit the 50% threshold (30 units) at $125 until 60 units which equals $7500.00 . Everything after 60 units is $200 each with no cap.
I'm trying to find a formula that as they enter their weekly totals it calculates at the correct rate meaning as they go along it shows no commission earned until 30 units are entered and then it steps up again at 60... is this even possible?

All I have so far is a simple lookup tool that tells them the rate at which they are being paid out using VLOOKUP

=VLOOKUP(C10,$C$4:$D$6,2,TRUE)

[TABLE="class: cms_table, width: 319"]
<tbody>[TR]
[TD]Sales Tiers[/TD]
[TD]Tier Minimum[/TD]
[TD]Payout Rate[/TD]
[/TR]
[TR]
[TD]0-30[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]31-59[/TD]
[TD]31[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]$60[/TD]
[TD]60[/TD]
[TD]$200[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

David

 

[Dave Patton]

Attention Biz

snakel87's requirement was not clear; the later suggestions probably address his question.
Since there a only two rates, the suggestion just above is sufficient.

Excel 2010
	A	B	C	D	E	F
1	Tier	Rate	Diff		Cumulative	Bonus
2	0	125	125		30	0.00
3	60	200	75		60	7,500.00
4					72	9,900.00
5					500	95,500.00
6
7aa

Cell Formulas
Range	Formula
C2	=B2-N(B1)
C3	=B3-N(B2)
F2	=(E2>30)*SUMPRODUCT(--(E2>$A$2:$A$3),(E2-$A$2:$A$3),$C$2:$C$3)
F3	=(E3>30)*SUMPRODUCT(--(E3>$A$2:$A$3),(E3-$A$2:$A$3),$C$2:$C$3)
F4	=(E4>30)*SUMPRODUCT(--(E4>$A$2:$A$3),(E4-$A$2:$A$3),$C$2:$C$3)
F5	=(E5>30)*SUMPRODUCT(--(E5>$A$2:$A$3),(E5-$A$2:$A$3),$C$2:$C$3)

 

[Biz]

Attention Biz

snakel87's requirement was not clear; the later suggestions probably address his question.
Since there a only two rates, the suggestion just above is sufficient.


Excel 2010

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Tier[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: center"]Diff[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Cumulative[/TD]
[TD="align: center"]Bonus[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]7,500.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]9,900.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]95,500.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

7aa​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=B2-N(B1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=B3-N(B2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=(E2>30)*SUMPRODUCT(--(E2>$A$2:$A$3),(E2-$A$2:$A$3),$C$2:$C$3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=(E3>30)*SUMPRODUCT(--(E3>$A$2:$A$3),(E3-$A$2:$A$3),$C$2:$C$3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=(E4>30)*SUMPRODUCT(--(E4>$A$2:$A$3),(E4-$A$2:$A$3),$C$2:$C$3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=(E5>30)*SUMPRODUCT(--(E5>$A$2:$A$3),(E5-$A$2:$A$3),$C$2:$C$3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Dave,
Thank you for your help. It was a twisted question.

Kind Regards

Biz