Forum Discussion
Conditional formulas with multiple outcomes
Hey guys,
Trying to write a costing calculator, however I'm getting stuck using IF, AND, and OR. We have a set of products, some are eligible for discount, and some are not. Those that are eligible, the size of the discount depends on the TOTAL number of users across the board (i.e. not just that one product). 1 user = no discount, 2 = 20%, 3 = 30%, 4+ = 35%
I.e.
Hey guys, Trying to write a costing calculator, however I'm getting stuck using IF, AND, and OR. We have a set of products, some are eligible for discount, and some are not. Those that are eligible, the size of the discount depends on the TOTAL number of users across the board (i.e. not just that one product). 1 user = no discount, 2 = 20%, 3 = 30%, 4+ = 35%
I.e.
| Product | Cost | Discount | Users |
| Product A | $15.00 | No | 2 |
| Product B | $65.00 | Yes | 3 |
| Product C | $70.00 | Yes | 5 |
| Product D | $25.00 | No | 1 |
Total Users: 11
Eligible for Discount: 8
Discount amount on eligible amount: 35%
TOTAL amount: $600
Amount eligible for discount: $545
If there were only 2 total eligible products, then the total discount amount would be 20%
cheers
1 Reply
Hi MasonJD
if I understood everything correctly, this could be a solution:
Please note, that you need the replace ; with , in all formulas, based on your local regional settings.
