multiply value based on quantity

[Bigfoot3910]

I am trying to calculate a discount price based on quantity purchased. For
example if someone buys 20 widgets they pay $4.00 each for them, if they buy
50 widgets they pay $4.00 each for the first 20, then $3.50 each for the next
30, 100 widgets - $4.00 first 20, $3.50 next 30 and $3.25 for the other
50..............

Any hope?

 

[Per Jessen]

Here's a way with quantity in A2:

=MIN(A2,20)*4+MAX(MIN(A2-20,30),0)*3.5+MAX(MIN(A2-50,50),0)*3.25

Regards,
Per

 

[Ashish Mathur]

Hi,

You may get some pointers from B) 1) from this link -
http://ashishmathur.com/articles.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Rosenfeld]

I am trying to calculate a discount price based on quantity purchased. For
example if someone buys 20 widgets they pay $4.00 each for them, if they buy
50 widgets they pay $4.00 each for the first 20, then $3.50 each for the next
30, 100 widgets - $4.00 first 20, $3.50 next 30 and $3.25 for the other
50..............

Any hope?

To set up a method of doing this that is easily maintainable, and extensible,
you could use a lookup table.

Somewhere on your worksheet set up a table. I NAME'd it "Tbl" (without quotes)

With your data, your table would look like:

0 $ 0.00 $4.00
20 $ 80.00 $3.50
50 $185.00 $3.25
....

Note that column 2 is the total cost of what came before. So if your table is
in I1:K3, then J2: =(I2-I1)*K1+J1 and you fill down as far as needed.

Then, with your quantity in A1, the total cost is given by:

=VLOOKUP(A1,Tbl,2)+(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3)
--ron