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


--
Daniel Sloan
------------------------------------------------------------------------
Daniel Sloan's Profile: http://www.excelforum.com/member.php...o&userid=28940
View this thread: http://www.excelforum.com/showthread...hreadid=486761

Daniel Sloan Wrote:
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(A175,A1<=150),A1*1.01,IF(OR(A 1150,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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=486761

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(C 1=A151,C1<300),0.02*C1,C1
*0.03)))

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


"Daniel Sloan"
wrote in message
news:Daniel.Sloan.1yu5fg_1132557630.8885@excelforu m-nospam.com...

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


--
Daniel Sloan
------------------------------------------------------------------------
Daniel Sloan's Profile:
http://www.excelforum.com/member.php...o&userid=28940
View this thread: http://www.excelforum.com/showthread...hreadid=486761

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


--
Daniel Sloan
------------------------------------------------------------------------
Daniel Sloan's Profile: http://www.excelforum.com/member.php...o&userid=28940
View this thread: http://www.excelforum.com/showthread...hreadid=486761

Another way:

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

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

=A1*((A175)*CEILING(A1,150)/150)/100

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Daniel Sloan"
wrote in message
news:Daniel.Sloan.1yu5fg_1132557630.8885@excelforu m-nospam.com...

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


--
Daniel Sloan
------------------------------------------------------------------------
Daniel Sloan's Profile:
http://www.excelforum.com/member.php...o&userid=28940
View this thread: http://www.excelforum.com/showthread...hreadid=486761

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


--
Daniel Sloan
------------------------------------------------------------------------
Daniel Sloan's Profile: http://www.excelforum.com/member.php...o&userid=28940
View this thread: http://www.excelforum.com/showthread...hreadid=486761