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

I am trying to create a pricing sheet that will include a cell for discount
percentages. The criteria a 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.

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

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

"bearkm02" wrote in message
...
I am trying to create a pricing sheet that will include a cell for discount
percentages. The criteria a 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.

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

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

"bearkm02" wrote:

I am trying to create a pricing sheet that will include a cell for discount
percentages. The criteria a 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.

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

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

But if this is not the case, look he

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


--
Kind regards,

Niek Otten

"bpeltzer" wrote in message ...
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

"bearkm02" wrote:

I am trying to create a pricing sheet that will include a cell for discount
percentages. The criteria a 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.