Search code examples
exceldiscounts

EXCEL: Make a formula to discount pricing.

We are making a spreadsheet for our dealers to order our product. The dealers get different discounts if they order more.

Example: Orders below $2,000 they receive 15%. Orders 2000-2999 get 20%. Orders above 3000 get 23% off.

It would be a formula in the total cell we have in place. When the total reaches above the certain numbers it would discount the total, so maybe a gross cell with the total then maybe a couple other cells that trigger the discounts if they meet the number would be the easiest?

Any one have any ideas?

Thanks!

Solution

  • If A1 has your "gross", put this in your "net" cell. This will do 15% discount for < $2k, 20% discount $2k-$3k, and 30% discount over 3k. You can continue nesting as necessary for discount levels.

    =IF(A1<2000,A1*0.85,IF(A1<3000,A1*0.8,A1*0.7))