Pricing Calculator

[Swanky]

G'day Guys.

I'm looking for something I think might be simple for a change.

I work in a quarry, dispatching our quarry products the local population.

I'm making a "simple" spreadsheet that will allow me to calculate the cost of carting a product to a particular location.

Normally when quoting a price I will look through my little book of products, find the one they are after and get the price, then find out what suburb or town they live in, look up how many kilometres it is and then look at our pricing chart for the kms, get that price and then add it to the product price and that is their price per tonne for that product delivered.

Make sense?

eg:

Customer Smith wants 10t of Quarry Sand delivered to Smith Town 10kms away.

Sand is $15.00 per tonne.
10kms cartage is $5.00

Therefore the price for Mr. Smith is $20 per tonne. (We always quote per tonne as we can't guarantee we will get exactly 10t)

So I have set up a spread sheet, assigning a number to all the products 1 through to 11.
I have also assigned a number to all the towns we deliver to (1 through to 68).


The idea being that I punch a number that corresponds to a product in 1 cell and in the adjacent cell it brings ups the price.

I then punch in a number that corresponds to the town and it brings up the kms in the adjacent cell.

Then a quick Sum adds the 2 numbers together for the price.

Make sense?

So if I have this data in my spreadsheet:

1. Dry Sand $9
2. Wet Sand $10

1. Smith Town 10kms $5.00
2. Jones Town 14kms $6.00

When I punch in 2 in to Products cell it will give me $10 and when I punch in 1 into the KMS cell it will give me the price $15.

Any thoughts on how I could do this?

Cheers.

:D

 

[Willmonbo]

perhaps there may be a better method to do this altogether, but to follow your way of doing this, why don't you just do a vlookup to both lists and then sum the two results.

 

[Dryver14]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></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: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD>dry sand</TD><TD style="TEXT-ALIGN: right">9</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD>Smithtown</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD>wet sand</TD><TD style="TEXT-ALIGN: right">10</TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD><TD>Marks Town</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD>X gravel</TD><TD style="TEXT-ALIGN: right">11</TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD>Freds Town</TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Y Gravel</TD><TD style="TEXT-ALIGN: right">12</TD><TD> </TD><TD style="TEXT-ALIGN: right">4</TD><TD>Johns Town</TD><TD style="TEXT-ALIGN: right">9</TD></TR></TBODY></TABLE>

formula in B1 is =VLOOKUP(A1,E1:G4,3,0) where you input the code for gear in A1

formula in B2 is =VLOOKUP(A2,I1:K4,3,0) where you input the code for place

then in B3 =Sum(B1:B2) format as money

 

[Swanky]

Thanks guys, I don't know great deal about VLookups, but today I'm learning something new!

I have made the simple version you have made Dryver and with a bit of playing around, I have figured out how to get it to work with my spreadsheet.

thanks guys, once again, the Mr. Excel forumers have hit a home run.

I never want you guys to do the work for me, I always want to learn something, and once again you guys have pointed me in the right direction.

Thanks guys!

:D