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!

 

[sheetspread]

Use vlookup or index/match

 

[elvgames]

alternatively you might be able to generate a simple formula using an 'if / then' statement. Can you share the graph in your book or provide a range of sample dimentions with prices?

 

[Nalani]

Welcome to the Board GuybrushThreep

Without provideing how your data is laid out, leads to alot of speculation.

You can download one of the tool listed at the bottom of my reply to do so like I am doing.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1								column	row
2		1000mm	1100mm	1200mm	1500mm	1600mm		1500mm	900mm
3	500mm	$1.00	$2.00	$3.00	$4.00	$5.00
4	600mm	$2.00	$4.00	$6.00	$8.00	$10.00		$20.00	results
5	800mm	$4.00	$8.00	$12.00	$16.00	$20.00
6	900mm	$5.00	$10.00	$15.00	$20.00	$30.00
7	950mm	$3.00	$6.00	$9.00	$12.00	$15.00
Sheet1

This is just a guess on your layout. Hope it helps.

 

[GuybrushThreep]

Yes that sheet is effectively what I'm referring to. Ideally it would be hidden, and the only functional items visible would be the input fields.

I'm not sure how to post a spreadsheet here, but the function would be like this:

step 1) Its all blank and data is yet to be entered

Item#|Width|Height| Price
1

step 2) You enter your width and height

Item#|Width|Height|Price
1 | 1500 | 900 |

step 3) As soon as you hit "enter" it goes to the next item, and a price was automatically selected for the first item

Item#|Width|Height|Price
1 | 1500 | 900 | $20
2

 

[Nalani]

I'm not sure how to post a spreadsheet here

You can not post a spreadsheet. But you can post a small sample of your data layout and your expected results as I stated above.

You need to download one of the two addins listed at the bottom of my reply.

So from what I'm gathering, you have your Data on a one sheet, and you want to input the dimensions on a seperate sheet and come up with the price. Something like this:

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Item #</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Width</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Height</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Price</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">1500mm</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">900mm</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">$20.00</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I have a tendency to try and read minds and help out new members.

But I (we) really need your input and your actual Data to work out the best solution. Sometimes I get way over my head because I am not all that good at solving the issue.

Try an download one of the programs, show a few rows of your data, and a few rows of what your results should look like.

 

[GuybrushThreep]

Here is an example price list:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: center; font-weight: bold;" colspan="5">Window Prices (Height x Width)</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="font-weight: bold;">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right; background-color: rgb(153, 204, 255); font-style: italic; font-weight: bold;">mm</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">1000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">2000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">3000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">4000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">5000</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">500</td> <td style="text-align: right;">$20.00</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">600</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$70.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">700</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$55.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$80.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">800</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$90.00</td> <td style="text-align: right;">$100.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">900</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$80.00</td> <td style="text-align: right;">$100.00</td> <td style="text-align: right;">$120.00</td> <td style="text-align: right;">$160.00</td></tr> </tbody></table>

Here is the desired FRONT END:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 92px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="text-align: center; font-weight: bold;" colspan="3">Please enter all windows:</td> <td>
</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Item #</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Width</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Height</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Price</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td style="text-align: center; font-style: italic; font-weight: bold;">1</td> <td>
</td> <td>
</td> <td>
</td> </tr></tbody></table>

So, after entering the width and height, the price is automatically found:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 92px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td style="text-align: center; font-weight: bold;" colspan="3">Please enter all windows:</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(153, 204, 255); font-style: italic; font-weight: bold;">mm</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">1000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">2000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">3000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">4000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">5000</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">27</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Item #</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Width</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Height</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Price</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">500</td> <td style="text-align: right;">$20.00</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">28</td> <td style="text-align: center; font-style: italic; font-weight: bold;">1</td> <td style="text-align: center;">2000</td> <td style="text-align: center;">800</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">600</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$70.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">29</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">700</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$55.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$80.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">30</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">800</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right; color: rgb(255, 0, 0); font-weight: bold;">$60.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$90.00</td> <td style="text-align: right;">$100.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">31</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">900</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$80.00</td> <td style="text-align: right;">$100.00</td> <td style="text-align: right;">$120.00</td> <td style="text-align: right;">$160.00</td></tr></tbody></table>

From here you should be able to enter as many times as you like...

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">37</td> <td style="text-align: center; font-weight: bold;" colspan="3">Please enter all windows:</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">38</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">39</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Item #</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Width</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Height</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Price</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">40</td> <td style="text-align: center; font-style: italic; font-weight: bold;">1</td> <td style="text-align: center;">2000</td> <td style="text-align: center;">800</td> <td style="text-align: right;">$60.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">41</td> <td style="text-align: center; font-style: italic; font-weight: bold;">2</td> <td style="font-style: italic;">
</td> <td>
</td> <td>
</td></tr></tbody></table>

So the problem is how to determine the price based on the two separate dimensions (width & height)???

 

[Nalani]

If you want everything on one sheet:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		Window Prices (Height x Width)
2								Item #	Width	Height	Price
3	mm	1000	2000	3000	4000	5000		1	2000	800	$60.00
4	500	$20.00	$30.00	$40.00	$50.00	$60.00		2	1000	600	$30.00
5	600	$30.00	$40.00	$50.00	$60.00	$70.00		3	5000	600	$70.00
6	700	$40.00	$50.00	$55.00	$70.00	$80.00
7	800	$50.00	$60.00	$70.00	$90.00	$100.00
8	900	$60.00	$80.00	$100.00	$120.00	$160.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
K3	=INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0))
K4	=INDEX($A$3:$F$8,MATCH(J4,$A$3:$A$8,0),MATCH(I4,$A$3:$F$3,0))
K5	=INDEX($A$3:$F$8,MATCH(J5,$A$3:$A$8,0),MATCH(I5,$A$3:$F$3,0))

If you want your lookup data on a different sheet:

Excel Workbook
	A	B	C	D
16	Please enter all windows:
17
18	Item #	Width	Height	Price
19	1	2000	800	$60.00
20	2	1000	600	$30.00
21	3	5000	600	$70.00
Sheet2

Excel 2007

Cell Formulas
Range	Formula
D19	=INDEX(Sheet1!$A$3:$F$8,MATCH(C19,Sheet1!$A$3:$A$8,0),MATCH(B19,Sheet1!$A$3:$F$3,0))
D20	=INDEX(Sheet1!$A$3:$F$8,MATCH(C20,Sheet1!$A$3:$A$8,0),MATCH(B20,Sheet1!$A$3:$F$3,0))
D21	=INDEX(Sheet1!$A$3:$F$8,MATCH(C21,Sheet1!$A$3:$A$8,0),MATCH(B21,Sheet1!$A$3:$F$3,0))

 

[GuybrushThreep]

Hey thats brilliant! I was thinking I'll have to create macros and such, but this is a perfect solution.

Many Many Thanks to all!

 

[Nalani]

Glad it worked. Thanks for the feedback.