IF Statement: Could some one please provide some pointers

[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 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

[Paul Sheppard]

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

[R.VENKATARAMAN]

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:[email protected]...
>
> 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]

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

[Sandy Mann]

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"Daniel Sloan" <[email protected]>
wrote in message
news:[email protected]...
>
> 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]

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.