Calculate Distance Between 2 Street Addresses

  • I have address stored in an excel spreadsheet. One address per row. I want to buy or develop a function which would compute, in miles the driving distance between 2 addresses. Typical use is to computen the distance between a Subject address and multiple destinations, all in one spreadsheet.

    I found a company which produces an addin fucntion for Excel which computers distance between Zip codes. However this isn't accurate enough. I need the distance between exact addresses (house #, street address, city, state, Zip).

    Any ideas how to provide this functionality?

  • Re: Compute Driving Distance

    Where are keeping all of the map information for the addresses
    Excel needs the data from a map source for the calculations

    "The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

  • Re: Compute Driving Distance

    Quote from jhenderson

    Where are keeping all of the map information for the addresses
    Excel needs the data from a map source for the calculations

    I download all address from a Real Estate Multi-list system into an Excel spreadsheet.


    1. Down load addresses in a format: house number, street address, city, state, zip code, all in the same row, but different columns for each..

    Could be dozens, one address per row.

    2. Add the Subject's address in the same format at the top of the grid.

    3. Here is the part I need -- san Excel function which compare the Subject's address to each target's address and returns the driving mileage as a value in miles. This value can be placed in a separate column, one value per row which would be the driving miles between that Target and the Subject.

    4. I can than sort the entire grid based on this distance and have the targets ranked by distance from subject.

    The work I'm doing requires target properties to be within a limited, pre-detrmined, driving radius.

    (NOTE: if it is easier I could live with the "as the crow flies" distance rathter than driving distance.

    I'm assuming a Google Map or Yahoo map API would need to be used?

  • Re: Calculate Distance Between 2 Street Addresses

    There are formulas for exactly measuring between two points. ...also quick ways to estimate driving distance. Neither method would properly account for unavoidable obstacles, since they're just simple formulas and not actually doing any sort of routing logic.

    However, in order for those formulas to work, they would need 2 points on a 2D plane expressed as (x,y) coordinate pairs. This assumes you're looking for relatively short distances and curvature of the Earth does not come into play. (usually a safe assumption)

    Street addresses mean absolutely nothing to these formulas... you would need the associated lat-long for the addresses to have any glimmer of hope for formula solutions within Excel; aside from buying some sort of third party mapping add-on (I don't know of any).

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]