how do i use a formula count of figures between to conditions

[aarif]

hi,

My question is, if a sales person gets his/her pay on different slabs of
target achieved than how to get the count of achieved figure by using a
formula in Microsoft Excel worksheet.

Incentive Slabs Percentage
Nil 0-40%
$10 40-50%
$15 50-60%
$20 60%+

Target given Achieved count of cases Percentage
200 140 70%

Incentive for the same
No. of cases Achieved % Payout he/she get (in Dollars)
80 cases >40% Nil
next 20 cases <40%>=50% $200 (20 cases * $10)
next 20 cases <50%>=60% $300 (20 cases * $15)
next 20 cases <60%+ $400 (20 cases * $20)

Total Achievment Percentage Total Payout
180 70% $900
I want to know how can i get seperate counts for all slabs by using
formulas, I had tried harder but stumped, Please help me for the same.

 

[bpeltzer]

Think of the problem instead as paying $10/unit for every unit over 40%
(*every* unit, not just up to 50%), PLUS $5/unit for every unit over 50%,
PLUS $5/unit for every unit over %60%.
Then, if the target is in B2 and the number sold in C2, the payout would be:
=MAX(0,C2-B2*0.4)*10 + MAX(0,C2-B2*0.5)*5 + MAX(0,C2-B2*0.6)*5
Each section calculates the number of units sold above the breakpoint (the
MAX(0,) ensures that we don't deduct pay for coming in below a breakpoint)
and multiplies that by the incremental unit pay in that tier.
HTH. --Bruce

 

[aarif]

hi,
I was not certain about receiving answer very quickly, I am very thankful
for helping me. Formula u gave is very useful to me I want to knot that if I
want to seperate count of cases with to conditions like <40% and >=50% then
how can i use furmula for the task,

Thanks & Regards,
Aarif