• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Greater than and less than in the same formula

T

TONYI8919

New Member
I have tried to figure this out and keep getting the wrong outcome.

I am in Sales and bonus is paid on a range of sales as listed below:

1-4 $0

5-9 $100 per

10+ $200 per (retro to unit 1)


I set up a "If" Function like this: =IF(C5>4,C5*100,IF(C5>9,0)) Cell C5 is units sold

This works well on units 0 to 9, but calculates 10 units at $100 per instead of Zero


I have another "IF" function for the range of 10+ which also works well.


What am I doing wrong??
 
Hi Tony,

In your IF statement, the first part (C5>4) would get evaluated to TRUE for all values greater than 4. (i.e. 5, 9, 10, 25, etc. would all return TRUE.) When TRUE, only the first part of the IF statement (C5*100) would get executed.


For your requirement, you would need to structure the IF statement similar to the following:

=IF(C5<=4,0,IF(C5<=9, (C5-4)*100, C5*200))


You can also reverse the logic as follows:

=IF(C5>=10,C5*200, IF(C5>=5, (C5-4)*100, 0))


Hope this helps.


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top