Cost Based On Distance Between Zip Codes

  • I have to design a spreadsheet that will return a dollar amount based in large part on the distance between two zip codes.

    I know I need a table or array of some sort that will include the distances between these zip codes, but am unsure how to go about looking that up. There are only a dozen or so zip codes to manage, so that's not the biggest part.

    Basically, this is a shipping question. I have a number of fixed costs (that I would like to be able to change to test different conditions, but will remain unchanged 99% of the time) and the one variable of distance. I want to be able to plug in three-digit zip codes in a to-from sort of way, and have Excel calculate a dollar amount.

    What is the best way to attack this?

  • Re: Cost Based On Distance Between Zip Codes

    I once attempted to use 5 digit zip code distances for calculating the variable part of shipping cost for 36 US shipping locations covering about 75% of the US area. For long haul its not to bad, but for local delivery its an exercise in futility.

    How do you calculate the distance of adjacent zip codes?
    Zip code areas very greatly in shape. Is the delivery in the center or someplace on peripheral?
    What about travel within a single zip code?
    Then there’s the allocation of travel cost for multiple drops.

    Of course now-a-days you have point to point mapping with mileage. But you have to pay for this data, and keep it updated

    Just my 2 cents

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!