Assistance required

[peterbata]

Hello all. I don't have very much experience with Excel but I am really trying to do my best. I am working on a small project and thought that Excel would fit the bill. I have run into a little snag (brain freeze). Here are some details.

A friend, who has a small courier company, has asked me to help out with putting together a customer price list. The list is made up of approx 100 postal codes. Using an Excel add-n (CDXZipStream) I'll be able to fetch the distance between all 100 postal codes. That works out to 10,000 cells. I'll call the worksheet that holds the fetched distances "calc". The process of going out to Bing for all the distances is excruciating long and can only be done if the add-in is installed locally. I would only want to do this once. Distributing the excel file would be difficult as the machine accessing the file would require the add-in. Not possible, and probably not necessary.

So, what I did was to convert the fecthed distances into whole numbers via roundup. So, 1.29 km becomes 2.0 km This information resides on a worksheet named "value". On another worksheet "rates" I created what I guess can be called a multiplier grid. Which looks something like this:

So any value that falls in between let's say 1 and 5 would use row 1 and it's corresponding rates.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DIST[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]Overnight[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6.00[/TD]
[TD]9.25[/TD]
[TD]13.75[/TD]
[TD]26.25[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7.00[/TD]
[TD]10.25[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8.00[/TD]
[TD]11.25[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]10.00[/TD]
[TD]12.25[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]

The actual price list which will be either printed or emailed to a client I named "client" This sheet has all 100 postal codes running down the first column titled "zone" To the right are columns with headings for delivery preference 8 hours, 4h, 2h and overnight.

Now, here is where I am stuck. If a client from postal H2L requests a price list, then obviously he would like to know the cost of shipping his parcel to any of the remaining 99 postal codes. I created a dropdown at the very top of the sheet which would allow the courier company to select the desired postal code. I can picture what should happen in my head but can't seem to translate that onto my worksheet(s).

1) Select postal code from dropdown
2) The "value" sheet is scanned for all possible combinations H2L -> XXX
3) All distances are calculated against the "rates" table
4) Price list shows all the possible dollar combinations.

I would have provided the worksheet but I don't think that there is a way for me to attach the file. Perhaps I can make it accessible offsite. By the way, what I am working on now only contains a limited combination of postal codes. 14RX14C

Any help and/or suggestions would be appreciated immensely. Thank you. Peter

 

[PCRIDE]

You could use IF(AND statements.

IF(AND(Distance>1,<5,8,), Price $,IF(AND(Distance>5,<10,4,), Price $

The problem you'll run into is the multitude of combinations. 14x14 would be 196 combos which you wouldn't be able to nest that many formula.

You might be better off doing this by flat rate by size of box. 14.95 in a flat rate box by Postal Office should ship anywhere in the US, that may not be very exciting for your close customers, but now days it seems anything is $10 to ship, unless your amazon.

 

[peterbata]

Thank you so much for the prompt reply. I really appreciate it. Actually, once the entire "value" table is populated with the Rounded Up info that would be more like 100X100. IF my math is correct that would be 10,000 combos. Could that be? I don't if this is possible. Couldn't I use an Index / Match and Vlookup formula?

1) find the intersecting data
2) crosscheck against the rates table
3) yield info into the price table.

https://mega.nz/#!IxIzXQgY!4jqQAuUFOu_WtV-a5XlGTIgJqHX5iSqqw2Hl4STEJnA

 

[peterbata]

Actually, this courier does not charge by weight or size. It's the distance and the delivery service that the client chooses that really matter.

Hence, the 8,4,2, or overnight delivery. Different rate for each. I am still trying to apply your formula to my worksheet. Having a little trouble figuring it out. But, I'll keep trying.

You could use IF(AND statements.

IF(AND(Distance>1,<5,8,), Price $,IF(AND(Distance>5,<10,4,), Price $

The problem you'll run into is the multitude of combinations. 14x14 would be 196 combos which you wouldn't be able to nest that many formula.

You might be better off doing this by flat rate by size of box. 14.95 in a flat rate box by Postal Office should ship anywhere in the US, that may not be very exciting for your close customers, but now days it seems anything is $10 to ship, unless your amazon.