Incorporating an automatic pricing system

[Josh88]

Hi,

I have a worksheet with a price system for different amounts of boxes ordered.
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Boxes Ordered[/TD]
[TD]Price per box[/TD]
[/TR]
[TR]
[TD]50-99[/TD]
[TD]$12.00[/TD]
[/TR]
[TR]
[TD]100-199
[/TD]
[TD]$11.40[/TD]
[/TR]
[TR]
[TD]200-299[/TD]
[TD]$10.80[/TD]
[/TR]
[TR]
[TD]300-399[/TD]
[TD]$10.20[/TD]
[/TR]
</tbody>[/TABLE]

In a separate worksheet in the same document I have all of my expenses and income forecast. In there I have a cell for the quantity of boxes ordered and the cell next to it stating the price per box.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Title[/TD]
[TD]Quantity[/TD]
[TD]Price per Box[/TD]
[TD]Final Price[/TD]
[/TR]
[TR]
[TD]Boxes Ordered[/TD]
[TD]85[/TD]
[TD][/TD]
[TD]=Quantity*Price per Box[/TD]
[/TR]
</tbody>[/TABLE]

I would like to set a formula that will automatically adjust the price per box in this worksheet as the quantity changes.
How can I do this?

Thank you very much

 

[FormR]

Hi, if you can change your lookup table to only have the lower bound quantities then you can use a simple LOOKUP() formula - for example:

Excel 2013/2016
	A	B	C	D	E	F	G
1	Boxes Ordered	Price per box		Title	Quantity	Price per Box	Final Price
2	50	12		Boxes Ordered	85	12	1020
3	100	11.4
4	200	10.8
5	300	10.2
Sheet1

Cell Formulas
Range	Formula
F2	=LOOKUP(E2,$A$2:$A$5,$B$2:$B$5)

 

[Josh88]

Thanks FormR. If I do want to maintain the lookup table in a different worksheet, can this still be used with a reference to the other worksheet?
=LOOKUP(E2, 'worksheet 2'!$A$2:$A$5,$B$2:$B$5)

 

[FormR]

can this still be used with a reference to the other worksheet?
=LOOKUP(E2, 'worksheet 2'!$A$2:$A$5,$B$2:$B$5)

Hi, yes - but you'll also need the sheet reference on the second range; i.e.

=LOOKUP(E2, 'Worksheet 2'!$A$2:$A$5,'Worksheet 2'!$B$2:$B$5)

 

[Josh88]

works perfectly! Thanks so much

 

[Josh88]

Hi,
I have recently introduced a new factor into my pricing system and can't quite work out how to automate it. Please help...

I have 3 elements which I am trying to use in the LOOKUP formula. I have quantity, length and per unit price. Here is a table of the information with quantity along the X axis and length down the Y:
[TABLE="class: grid, width: 350, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]2500[/TD]
[TD]5000[/TD]
[TD]10,000[/TD]
[TD]15,000[/TD]
[/TR]
[TR]
[TD]78m[/TD]
[TD]0.76[/TD]
[TD]0.73[/TD]
[TD]0.71[/TD]
[TD]0.70[/TD]
[/TR]
[TR]
[TD]90m
[/TD]
[TD]0.84[/TD]
[TD]0.81[/TD]
[TD]0.79[/TD]
[TD]0.78[/TD]
[/TR]
[TR]
[TD]92m[/TD]
[TD]0.85[/TD]
[TD]0.82[/TD]
[TD]0.81[/TD]
[TD]0.80[/TD]
[/TR]
</tbody>[/TABLE]








I would like to have an automated calculation whereby I can build a basic info box containing the number of units required and their length, and the matching unit price is inserted.

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Number of Units[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Length[/TD]
[TD]90m[/TD]
[/TR]
[TR]
[TD]Price per unit[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






Thank you very much Excel wizards!

 

[FormR]

Hi, I see that you have re-posted the new question here and have responses.

https://www.mrexcel.com/forum/excel...ted-cell-depending-2-factors.html#post4941259

 

[Josh88]

Thanks FormR, I reposted in a new thread to get a quicker response.