Forum Discussion
If/or/and formula
I am looking for a formula that will help me to choose my commission on sales based as follows, will be very grateful for your urgent help
| $75 | Up to $1000 |
| $150 | $1001 - $ 5000 |
| $225 | $5001 and above |
=IF(AND($D$1>0,$D$1<1001),75,
IF(AND($D$1>1000,$D$1<5001),150,
IF($D$1>=5001,225)))
Above nested IF formula works in my spreadsheet.
6 Replies
- How about this: =IFS(D1<=1000,75, D1<=5000,150, TRUE,225)
I put spaces between argument pairs because IFS evaluates the arguments in pairs.
This assumes several things: (1) the sales is in D1, (2) the sales can never be less than 0. The IFS works like this: Each pair of arguments goes together and the pairs are evaluated from left to right. The first pair is D1<=1000, 75. If the first argument in the first pair is true (D1<=1000), return the second argument in the pair (75) and stop evaluating any more of the arguments. If the first argument in the first pair is false, look at the next pair of arguments: D1<=5000, 150. If the first argument of that pair (D1<=5000) is true, return the second argument in the pair (150) and stop evaluating any more of the arguments. If this fails, go to the last pair of arguments (TRUE, 225): Since the first argument is true (TRUE), return the second argument in the pair (225). The first argument in the last pair is typically TRUE to make sure that the last value is returned.- Thank you. I will check it out with a live example. Many thanks for the quick response
Thank you for the speedy reply but what I need is a self-contained formula that can return the commission value based on the variable. I started doing the formula but got stuck halfway through. Here is my attempt at it, I am sure you will be able to complete it. The formula is in B6. Please help
=IF(OR(AND($D$1>0,$D$1<1001), AND($D$1>1000,$D$1<5001)),75,150)
=IF(AND($D$1>0,$D$1<1001),75,
IF(AND($D$1>1000,$D$1<5001),150,
IF($D$1>=5001,225)))
Above nested IF formula works in my spreadsheet.
