EXCEL: Make a formula to discount pricing.

Question

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!

Answer 1

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))

Answer 2

If you want to manage your discount table more easily: create a small table with your discount thresholds and your discount rates, then use vlookup to reference them. Example with the following discount table in cells G1:H3:

    0        15%
 2000        20%
 3000        23%

Now in your formula, assuming your sales volume is in A1, you can retrieve the discount rate with =vlookup(A1,$G$1:$H$3,2). If your discount structure changes, or if you want to add another tier, just extend the table accordingly.

Answer 3

Suppose A1 is the total amount of the order. Please put this formula in A2 where it will show the discount as desired. Net amount will be in the Cell A3 = A1-A2 =IF(A1>=3000, ROUND(A1*0.23,0), IF(A1>=2000, ROUND(A1*0.2,0), ROUND(A1*0.15,0)))