How do I create a formula for multiple levels of discounts?

[Guest]

I am trying to create a pricing sheet that will include a cell for discount
percentages. The criteria are: if material in square inches (cell D18) is
between 288 and 288 square inches, then a disount of 10% is subtracted from
the subtotal. If material in cell D18 is greater than 488 square inches,
then a disount of 15% is subtracted from the subtotal. I am a novice at
this, so I need some help from the experts.

 

[Peo Sjoblom]

I am assuming you made a typo and that you want

less than 288 no discounts
greater than or equal to 288 AND less than 488 10% discount
greater than or equal to 488 15%

I assume that subtotal amount is in E18

then something like this

=E18*(1-IF(D18="",0,LOOKUP(D18,{0;288;488},{0;0.1;0.15})))


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Guest]

The discount % can be calculated as =if(d2>=488,15%,if(d2>=288,10%,0))
(you may want to adjust the >= inequalities to >, dpending on precisely
where you want the discount to kick in). BTW, this assumes that the
discount, if applicable, applies back to the first square inch.
--Bruce

 

[Niek Otten]

<this assumes that the discount, if applicable, applies back to the first square inch.>

But if this is not the case, look here:

http://www.mcgimpsey.com/excel/variablerate.html