Auto-Calculate price based on width x height

[GuybrushThreep]

G'Day! I'm trying to create a excel sheet which will automatically calculate a price based on a given width or height. At the moment we calculate price by manually looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.

E.G.
you enter width of 1500mm in one field, then 900mm in another field, and next to that it automatically shows the price (based on some price list on another spreadsheet or workbook).

Any Help or Advice?
Cheers Mates!

 

[GuybrushThreep]

Hi, I've set up my tables and data, and everything works, but there is 1 problem. The width's increment in different sizes, for example:

WIDTH = 345mm | 575mm | 1610mm

IF I select a width of say, 345 and a height of 500, THEN it shows up with the appropriate price. However, IF I choose a width of 344 and a height of 500, THEN I get a #N/A in the price field.

Thats because I'm comparing JUST the width to the height, and IF its not 100% same then its a problem. I need to work out a solution that: if I enter a value in-between, then it will round the price up to the next price bracket. Make Sense?

Any suggestions on how I can do that?

 

[KeelySG]

Hi, I've set up my tables and data, and everything works, but there is 1 problem. The width's increment in different sizes, for example:

WIDTH = 345mm | 575mm | 1610mm

IF I select a width of say, 345 and a height of 500, THEN it shows up with the appropriate price. However, IF I choose a width of 344 and a height of 500, THEN I get a #N/A in the price field.

Thats because I'm comparing JUST the width to the height, and IF its not 100% same then its a problem. I need to work out a solution that: if I enter a value in-between, then it will round the price up to the next price bracket. Make Sense?

Any suggestions on how I can do that?

Hi folks, sorry for resurrecting an ancient post but this is exactly my issue today. Can anyone help to resolve it?

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1							1000	2000	3000	4000	5000
2	Item #	Width	Height	Price		500	$20.00	$30.00	$40.00	$50.00	$60.00
3	1	2321	888	60		600	$30.00	$40.00	$50.00	$60.00	$70.00
4						700	$40.00	$50.00	$55.00	$70.00	$80.00
5						800	$50.00	$60.00	$70.00	$90.00	$100.00
6						900	$60.00	$80.00	$100.00	$120.00	$160.00
Sheet1

Cell Formulas
Range	Formula
D3	=VLOOKUP(C3,$F$2:$K$6,LOOKUP(B3,$G$1:$K$1,COLUMN($G$1:$K$1)-5))

If you want to round in the other direction shift/change the corresponding prices, starting from n=0.

 

[sheetspread]

This works too:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1							1000	2000	3000	4000	5000
2	Item #	Width	Height	Price		500	$20.00	$30.00	$40.00	$50.00	$60.00
3	1	211	871	60		600	$30.00	$40.00	$50.00	$60.00	$70.00
4						700	$40.00	$50.00	$55.00	$70.00	$80.00
5						800	$50.00	$60.00	$70.00	$90.00	$100.00
6						900	$60.00	$80.00	$100.00	$120.00	$160.00
Sheet1

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(--($G$1:$K$1=CEILING(B3,1000))*--($F$2:$F$6=CEILING(C3,100))*$G$2:$K$6)

 

[KeelySG]

Thanks @sheetspread - I can't seem to get your last example to work, I get a #VALUE ! error?

What I'm trying to do (ultimately) is select the next price range if you're over the figures shown in purple. So selecting 1000 width x 500 height would give $20 (Correct), but if I selected 1001 width x 501 height I'd want to show $40

Hope that makes sense?

 

[sheetspread]

It worked in my test:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1							1000	2000	3000	4000	5000
2	Item #	Width	Height	Price		500	$20.00	$30.00	$40.00	$50.00	$60.00
3	1	1000	500	20		600	$30.00	$40.00	$50.00	$60.00	$70.00
4		1001	501	40		700	$40.00	$50.00	$55.00	$70.00	$80.00
5						800	$50.00	$60.00	$70.00	$90.00	$100.00
6						900	$60.00	$80.00	$100.00	$120.00	$160.00
Sheet1

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(--($G$1:$K$1=CEILING(B3,1000))*--($F$2:$F$6=CEILING(C3,100))*$G$2:$K$6)
D4	=SUMPRODUCT(--($G$1:$K$1=CEILING(B4,1000))*--($F$2:$F$6=CEILING(C4,100))*$G$2:$K$6)

 

[KeelySG]

Would it make a difference if I'm using Excel from Office 365?

 

[sheetspread]

I wouldn't expect it to, but can't say for sure. Have you copied the above exactly?

 

[sheetspread]

If your data was downloaded then you might have to convert it from numbers stored as text to actual numbers, which is easy.

 

[Lauretta]

Hi All,

Trying my luck here as this is an old thread but 99% of it is relevant for me. The only trouble I'm having is my width and height sizes vary as we sell made-to-measure. Essentially the measurements are in mms and I'm wanting use the sheets for quoting. How do I change the column names to reflect a range of numbers? ie 0-1000, 1001-1100, 1101-1200 etc

Appreciate any help