Conditional formulas with multiple outcomes

Occasional Visitor

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.

ProductCostDiscountUsers
Product A$15.00No2
Product B$65.00Yes3
Product C$70.00Yes5
Product D$25.00No1

 

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:

Martin_Weiss_1-1665037675696.png

 

Please note, that you need the replace ; with , in all formulas, based on your local regional settings.