IF Statement

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
 
P

Paul Sheppard

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
Click to expand...

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
 
D

Daniel Sloan

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 :)
 
D

Daniel Sloan

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. :)
 
Top