formula for various discounts

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Looking for a formula to determine discounts for various
weight thresholds, but would like the answer in one cell.
example
below 4000 lbs 0 discount
between 4000 & 7000 lbs .05 discount
between 7000 & 10000 lbs .10 discount
above 10000 lbs .15 discount.
 
First a question: Do the discounts apply to the entire weight or
just the weight in the band.

i.e., if 5000 lbs, does the discount apply to 5000 lbs. or 1000 lbs.?

If 5000, the discount can be found as:

= A1 * LOOKUP(A1,{0,4000,7000,10000},{0,0.05,0.1,0.15})

where A1 holds the weight. Of course, it would be more flexible to
put the weights and discounts that are in the formula into a table
and use VLOOKUP - that way you could change the table rather than
editing your formulae.

If the discount would only apply to 1000 lbs, the discount can be
found as:

=SUMPRODUCT(--(A1>{4000,7000,10000}),(A1-{4000,7000,10000}),{0.05,0.0
5,0.05})

for which you can see an explanation at:

http://www.mcgimpsey.com/excel/taxvariablerate.html
 
Let's say that your total cost is in C1 and your number of pounds is in B1.
In D1 (your discount cell) put the following IF statement:

=IF(B1>10000,C1*.15,IF(B1>6999,C1*.1,IF(B1>3999,C1*.05,C1*0))), which
basically says:

If weight is > 10000 set the discount equal to C1 times .15, otherwise if it
is between 7000 and 10000 pounds set the discount equal to C1 times .1,
otherwise if it is between 4000 and 6999 set the discount equal to C1 times
..05. If all of these are false, there is no discount (equal to 0).

You can have up to 7 of these nested IF statements in one expression. Any
more than that and you will need to use something like VLOOKUP. Keep in
mind that there are more "eloquent" ways to do this, but at least this will
make sense since it uses simple logic.

Bill Foley
www.pttinc.com
 
Back
Top