UPS / Fedex Zone Calculations

[snuffnchess]

Hi there!

UPS and Fedex both have zone calculators available where I can type in my starting zip code, and the website will tell us based off of that initial zip what shipping "zone" other parts of the country are in.

What I want to create is the ability to type in two random zip codes in excel, and have there be a calculation done through to tell me what zone the shipment would be considered. Everything is based on the first 3 digits of a the Origin zip code and the first 3 digits of the destination zip code

A link to the spreadsheet can be found at http://www.carpenoctem.tv/zonechartsample.xlsx

On the spreadsheet, I have added what two of the 708 different possible zone charts would be (tab 91000-91899) and samples of what we would want the final calculations to look like (tab zone calc)


Obviously, i realize that 708 charts at 211 (+/-) lines each chart, is A LOT of data. What would be the best way to go about doing this? At this point it is something we are looking to do ONLY for ground shipping, but in theory, would be wanting to do for express as well (possibly have each service on a different tab???)

 

[svendiamond]

First I would ask why the only options for Origin Zip are 91000-91899 and 19700-19899?

But anyway if what you want to do is find the ZONE that matches the first three digits in the Origin Zip AND the first three digits in the Destination Zip, it's going to be a bit of a process. First, the table has some destination zips like "###" and some like "###-###" -- so you'd have to have a little IF function to say if column B is "###" then look it up that way, but if it's "###-###" then look up in between those two numbers. That's kind of confusing and is going to make for a formula I really don't want to try to put together. It might be easier to make a new table where you just include all zip codes. Maybe go to Free ZIP code map, zip code lookup, and shipping comparisons and download their zip code database. So copy all the zip codes to a new table... index/match to find the zones for 19700-19899 and the same for 9100-91899... THEN you can do an easy INDEX/MATCH/IF to find which ZONE matches both the Origin Zip and Destination Zip.

 

[swjohanson]

Hi Snuffin--did you ever find a solution to this? I actually have the tables for Fedex (FedEx Rate Tools), I just cannot get the master tables for UPS.

Steve

First I would ask why the only options for Origin Zip are 91000-91899 and 19700-19899?

But anyway if what you want to do is find the ZONE that matches the first three digits in the Origin Zip AND the first three digits in the Destination Zip, it's going to be a bit of a process. First, the table has some destination zips like "###" and some like "###-###" -- so you'd have to have a little IF function to say if column B is "###" then look it up that way, but if it's "###-###" then look up in between those two numbers. That's kind of confusing and is going to make for a formula I really don't want to try to put together. It might be easier to make a new table where you just include all zip codes. Maybe go to Free ZIP code map, zip code lookup, and shipping comparisons and download their zip code database. So copy all the zip codes to a new table... index/match to find the zones for 19700-19899 and the same for 9100-91899... THEN you can do an easy INDEX/MATCH/IF to find which ZONE matches both the Origin Zip and Destination Zip.

 

[snuffnchess]

Hi Snuffin--did you ever find a solution to this? I actually have the tables for Fedex (FedEx Rate Tools), I just cannot get the master tables for UPS.

Steve

yes, currently have a zone calculation, and rate calculation tool up and running for UPS. It has been working great so far. I even updated to accomodate the 2016 rates and all worked perfectly with that.

The one problem is that there is just SO MUCH information to create this tool, that it gets bogged down sometimes. Just data alone is about 4mb.... not taking into consideration the calculations. PM me, maybe I can help you out with what you are working on??

 

[Smitty]

The one problem is that there is just SO MUCH information to create this tool, that it gets bogged down sometimes. Just data alone is about 4mb.... not taking into consideration the calculations.

Have you considered using PowerPivot? Or Access...

 

[snuffnchess]

I have not. At least for my purposes, we were trying to create a rating tool that would stand on its own... and not rely on other programs to work. I would love to get it into an SQL database, so that it would be easier to update the information and always have the most up to date rates, etc... but i have no idea on how to program things like that.

 

[snuffnchess]

Update: I did finally end up throwing the zone chart into access, and then having Excel reference the DB when the calculations are needed. Not only did the excel file go from 4MB to about 400kb, but it also operates A LOT faster. Calculations went from 20-45 seconds to 3-7 seconds