Commision Plans (again)

Posted by Steve Booth on July 19, 2001 11:11 PM

After looker at the archive, it helped, but Im still stuck. I am having trouble with an array(?) statement and am looking to be pushed in the right direction

Its a commision problem

If a salesman sells 5000 or less, he gets zero commision, if he sells between 5001 and 15000 he gets 5% (ie he gets 5% of 10,000), if he sells between 15001 and 25000 he gets 20% of that 10,000 plus 5% of the first 10,000) if he sells above 25001, he gets 25% of anything above 25001, plus 5% of the first 10,000 and 20% of the next 10,000)

Hope that makes sense, the trick being he only gets the percentage on that range, not the whole amount, and for the first 5000 he gets nothing:

I worked that out to be a following formula:

If D7 >=5000,E7 =sum(D7*0%)
If D7 &LT;=5000 and >=15000 then E7=sum(D7*5%)
If D7 &LT;=15001 and >=25000 then E7 =sum((D7*20%)-750)
If D7 &LT;=25001 then E7=sum((D7*25%)-2750)

however, its obviously wrong, but not obvious to me!!!!

any help would be greatly appreciated

Posted by Aladin Akyurek on July 20, 2001 1:01 AM

Steve,

Care to provide hand-calculated commissions (amounts) for the following sales:

6,700.00
18,000.50
32,000.00

Just to avoid confusing questions, I'd suggest using ordinary maths. E.g.:

6,700.00 => 6,700.00 * 5% = 335.00

Aladin

==============

Posted by Barrie Davidson on July 20, 2001 2:18 PM

Steve, if I understand your commission structure, the following formula will work for you (assuming sales are in cell A1).

=IF(A1>5000,IF(A1>15000,IF(A1>25000,(A1-25000)*25%+10000*20%+10000*5%,(A1-15000)*20%+10000*5%),(A1-5000)*5%),0)

Barrie

Posted by Steve Booth on July 22, 2001 11:10 PM

Thanks for your help, worked a treat:

Steve, Care to provide hand-calculated commissions (amounts) for the following sales: 6,700.00

Posted by Aladin Akyurek on July 22, 2001 11:51 PM

You meant...

to thank Barrie! Thanks for your help, worked a treat: