IF Statement

  • Thread starter Thread starter Daniel Sloan
  • Start date Start date
D

Daniel Sloan

I am attempting to use an IF formula to do the following:

IF sales are less than $75 no commission
IF sales are >=$76 but < $150 1% commission
IF sales are>=$151 but <$300 2% commision
IF sales are> $300 3% Commission

The Commission paid is stepped at each point and any amount in betwee
the breaks also must have the commission paid.

Could some one please provide some pointers as to the right way t
approach this.

Thanks

Danie
 
Daniel said:
I am attempting to use an IF formula to do the following:

IF sales are less than $75 no commission
IF sales are >=$76 but < $150 1% commission
IF sales are>=$151 but <$300 2% commision
IF sales are> $300 3% Commission

The Commission paid is stepped at each point and any amount in between
the breaks also must have the commission paid.

Could some one please provide some pointers as to the right way to
approach this.

Thanks

Daniel

Hi Daniel

Assuming the sales figure to be in cell A1, use this formula

=IF(A1<=75,A1,IF(OR(A1>75,A1<=150),A1*1.01,IF(OR(A1>150,A1<=300),A1*1.02,A1*1.03)))

Your explanation does not cater for someone getting $150 sales, so have
given $150 1%, you can change the formula if required
 
your value is in C1
in any other empy cell the formula is
=IF(C1<75,0,IF(AND(C1>=76,C1<150),0.01*C1,IF(AND(C1>=A151,C1<300),0.02*C1,C1
*0.03)))

check the results for various entries
chang e C1 to suit you


"Daniel Sloan" <[email protected]>
wrote in message
news:D[email protected]...
 
Thank you very much for your suggestions. The formula is held on my
laptop at work so I will get back to you when I reach the offfice in
approximately 12 hours. Thank you once again for your promt and
hopefully helpful suggestions :)
 
Another way:

=A1*((A1>75)*MIN(450,CEILING(A1,150))/150)/100

or if the commission continues in the same way after $300 then remove the
MIN function:

=A1*((A1>75)*CEILING(A1,150)/150)/100

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk


"Daniel Sloan" <[email protected]>
wrote in message
news:D[email protected]...
 
Thank you to all the people who replied in this thread. Your formula was
spot on and myself and my colleagues now have a quick, easy and
effective way of calculating commissions. Thanks again, regards,
Daniel. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top