Discount prices based on order quantity and package quantity

**jbrandau** · 11-23-2009, 05:07 PM

I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:

Sheet 1
A B C D E F G
1 Pack Qty Qty 1 Prc 1 Qty 2 Prc 2 Qty 3 Prc 3
2 100 1 0.9 100 0.74 400 0.6

Sheet 2
A B C D E F G
1 Pack Qty Qty 1 Prc 1 Qty 2 Prc 2 Qty 3 Prc 3
2 100 1 0.9 100 0.74 400 0.6

Sheet 1 formulas
Each Price Field Rules

If Quantity (one cell left) > than 24,999 = Corresponding price field (sheet 2)
If Quantity (one cell left) > 10,000 and < 25,000 = Corrisponding price field (sheet 2) *.9
If Quantity (one cell left) > 5,000 and < 10,000 = Corrisponding price field (sheet 2) *.85
If Quantity (one cell left) > 2,500 and < 5,000 = Corrisponding price field (sheet 2) *.8
If Quantity (one cell left) > 1,00 and < 2,500 = Corrisponding price field (sheet 2) *.75
If Quantity (one cell left) < or equal to package quantity = Corrisponding price field (sheet 2) *.70

Thanks!

**zbor** · 11-23-2009, 05:14 PM

Please, rename your title according to the rules

Hint: try to explain problem in title and not formulas that might be used

**zbor** · 11-23-2009, 05:29 PM

How about this?

=LOOKUP(A1,{0,1,2500,5000,10000,25000},{0.7,0.75,0.8,0.85,0.9,1})*B1

Adjust ranges where you need.

**jbrandau** · 11-23-2009, 05:39 PM

Thanks for the suggestion Zbor.

In your formula A1 is equal to the cell the the left of where I want the result (which has the quantity) correct?

Is B1 equal to the cell that contains the price that I would like to discount? Should this be an individual cell or a range?

Thanks,

John

**zbor** · 11-23-2009, 05:50 PM

Correct. Individual cell.

A1 is quantity wich give you factor with LOOKUP function (for example 0,85). Discount actually.
Then you mutliply it with price you need.

So you basically get 0,85*B1 but this factor is various depending on quantity.

Just pull formula down to all cells you need