Commision formula problem

  • Thread starter Thread starter chiwavdg
  • Start date Start date
C

chiwavdg

Here's what Im trying to do, and Im not having any luck. I need t
create a 4 teir commision plan in excel based on the profit margin %
which I show in the spread sheet. to that profit margin % I need t
apply the appropriate rate.

Here are the rates...

<5% Margin Apply 9% Rate
5% Margin <12% Margin Apply 18% Rate
12% Margin < 25% Margin Apply 28% rate
25% Margin Apply 34% rate
Click to expand...

I can't seem to get the if/then to recognise all the rates, I
obvioulsy doing something wrong...

HELP, I have to run Payroll tomorrow!!

Thank
 
Hi

if i'm understanding you correctly then the following formula should give
you what you're after - note, i've taken into account when the margin (A1)
=5%, =12%, =25% and when it's above 25%

=IF(A1<5%,A1*9%,IF(A1<12%,A1*18%,IF(A1<25%,A1*28%,A1*34%)))

Hope this helps

Cheers
JulieD
 
Hi

Try this

=IF(B8<=0.05,A8*0.09,IF(B8<0.12,A8*0.18,IF
(B8<=0.25,A8*0.28,IF(B8>0.25,A8*0.34,""))))

Watch the wrapping.

Regards
Peter
 
Back
Top