Incorporating an automatic pricing system

[Josh88]

Hi,

I have a worksheet with a price system for different amounts of boxes ordered.

Boxes Ordered	Price per box
50-99	$12.00
100-199	$11.40
200-299	$10.80
300-399	$10.20

<tbody>
</tbody>

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.

Title	Quantity	Price per Box	Final Price
Boxes Ordered	85		=Quantity*Price per Box

<tbody>
</tbody>

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:

	2500	5000	10,000	15,000
78m	0.76	0.73	0.71	0.70
90m	0.84	0.81	0.79	0.78
92m	0.85	0.82	0.81	0.80

<tbody>
</tbody>

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.

Number of Units	5000
Length	90m
Price per unit

<tbody>
</tbody>

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.