How to Find the Shortest or Quickest Routes using Excel Templates

We offer two Excel templates, using our software CDXZipStream or web service CDXGeoData, for calculating driving distance or time for a matrix (array) of locations.  A typical application for these templates would be calculating route data for a matrix of customers and stores.  Here is a short tutorial showing how this works with our web service CDXGeoData:

The template shown above automatically calculates driving distance or driving time for all combinations of locations listed. Once the calculations are complete, we can now go one step further and use simple Excel formulas to identify the specific store closest to each customer. In this case the store locations in the tutorial above are listed in row 9, in columns C through I, and customer addresses are listed in column B, starting in row 10.

To the right of the matrix data, enter the following in cell J10 for the first customer address:

=MATCH(MIN(C10:I10),C10:I10,0)+2

This equation identifies the column location of the lowest numerical value in cells C10 through I10, i.e. the shortest driving time for the first customer.  Note that the value “2” is added here since the data starts in the third column C.

To the right of the above equation, in cell K10, enter:

=INDIRECT(ADDRESS(9,J10))

This equation identifies the store address (in row 9) associated with the shortest driving time identified by the previous equation in cell J10.

Now just copy these equations along the rest of the customer list, and the address of the closest store is identified for each customer.

Note: If desired, the equations above can be combined like this into one cell: 

=INDIRECT(ADDRESS(9, MATCH(MIN(C10:I10),C10:I10,0)+2)) 

The CDXZipStream templates and CDXGeoData templates that perform driving distance and time calculations are free downloads from our CDXTech.com, and can be tested using the free CDXZipStream trial version or free CDXGeoData account access.

We also offer a template that calculates closest locations based on straight-line distance between ZIP Codes (for U.S. and Canada).   This offers a less exact analysis but is appropriate for very large sets of data where routing calculations may be prohibitively long.  Here’s a short video showing how the ZIP Code template works with CDXGeoData:

You can use the same equations described above to identify closest locations based on ZIP Code distance.

Geocoding and Reverse Geocoding with CDXGeoData

CDXGeoData is our location-based web service that can be accessed through a REST API when building your own website or app, or through powerful Excel templates that provide data directly to a spreadsheet. These easy-to-use templates employ CDXGeodata to verify address data, retrieve ZIP+4 codes, calculate driving distance and time, and now geocode addresses or reverse geocode points of latitude and longitude.

Particularly with the meteoric rise of mobile commerce, geocoding and reverse geocoding have become essential tools for location-based marketing and analysis. Here are two short tutorials on how to use CDXGeoData templates for geocoding and reverse geocoding:

CDXGeoData templates are particularly convenient in that no Excel add-in installation is required; just enter the CDXGeoData API key associated with your account, and you have full access to functionality.  And once you save the Excel template, the key is saved as well and does not have to be re-entered.  Since geolocation here uses Bing Maps as a data source, a Bing Maps key must be associated with the CDXGeoData account; please refer to our post How to Get a Bing Maps License Key for details.

The templates use Visual Basic for Applications to access CDXGeoData capability.  For the purposes of building your own app for geocoding or reverse geocoding, we also provide example code in C#, JavaScript, and VB.net.  This and other documentation are available through a free CDXGeoData account

CDXGeoData templates can be evaluated with the 1000 free data tokens provided monthly with each account.  These tokens can also be used for testing purposes during app or website development.  Additional tokens are available for use with large geocoding or reverse geocoding applications; please refer to CDXGeoData pricing for more information.

 

Straight-Line Distance as an Estimate for Driving Routes

CDXZipStream and CDXGeoData provide options for calculating either the straight-line ("as the crow flies") distance or driving (road) distance between locations. When selecting the distance function that best meets the need of an application, calculation time is also a factor; driving distance calculations are much more complex and take significantly longer, and may not be practical for very large data sets. Is straight-line distance a reasonable alternative? We know that straight-line distance always underestimates the actual length of a route (with the exception of routes along a perfectly straight road), but by how much?

To find out we performed an analysis using the functions in CDXGeoData. These functions are CDXGeoDistance for calculating straight-line distance, and CDXGeoRoute, which calculates travel distance and time using Bing Maps as the routing data source. We developed data sets where travel was within large regions in the contiguous U.S., as well as within specific counties. For each county, the start point for each route was at a single ZIP Code and the end point covered every other ZIP Code in the county. Here are the results:

Average straight-line underestimation is surprisingly consistent within regional and county areas, ranging about 11 to 18% and 18 to 23% less than the shortest route calculation, respectively.

Underestimation of route distance is greater when compared to the quickest route calculation, along with more standard deviation as well. Straight-line distance versus quickest route is a bit of an apple-to-oranges comparison, but is included here since quickest is usually the preferred travel route. For U.S. regions, straight-line distance versus quickest route is about 15 to 25% less, and excluding Los Angeles County (a probable outlier), county area straight-line distance compared to quickest route ranges about 26 to 30% less.

So for ball-park calculations, such as estimation of shipping costs, it would be realistic to assume straight-line distance plus up to about 30% additional mileage, depending on the route coverage area.  Straight-line distance is more accurate for longer routes probably because both local road and geographic restrictions – such rivers, lakes, parks, and other obstacles – are less important over longer distances. CDXGeodata uses a free Microsoft Excel template which completely automates straight-line distance calculation between ZIP Codes. Here is a short tutorial showing how it works:

For straight-line distance calculations using specific address locations, we offer an Excel template that works with CDXZipStream to generate reports of locations within a specific radius area.  Please watch the video below for a short tutorial:

When working with large data sets that require highly accurate routing analysis, we recommend a two-step process using straight-line distance calculations to first narrow down the list of candidate locations within a radius, then calculating the actual route distances from this smaller list.  Please refer to our post CDXZipStream Straight-line and Driving Distance Calculations for further discussion.

New CDX Technologies Apps for the Microsoft Windows Store

CDX Technologies is pleased to release a suite of apps that can be downloaded for free in the online Microsoft Store.  The tools will work on Microsoft Windows 8 and 10 operating systems where the Windows Store is available. Three apps have been released including Postal Address and Zip+4 lookup, Zip Code Report, and Demographic Report tools.  These apps match the applications that are also available on our web site under the “Tools” menu.  There is no charge to use these services.

The Postal Address app will let you enter a single or multi-line address to verify if the address is valid.  If valid the entered address will be corrected for spelling and formatting errors to be consistent with USPS standards.   Additional information will be included such as the county name, county FIPs, state FIPS, congressional district, timezone and more.

Our Zip Code Report app will generate a report that includes city, state, county and more for any zip code. Just enter your zip code and press the “Generate Report” button.  The report contains over 20 key zip code data items including latitude, longitude, area code and elevation. Use the "Print" button to save a copy of your data.

 

The Demographic Report app provides population, race, gender and location data based on zip code. Just select a report, enter your zip code and press the “Generate Report” button.  The reports contain over 50 key demographic statistics including household income, population, median age and business employment. Most the data is derived from the 2010 US Census. Click on the “Data Definitions” dropdown for an explanation of any item.

 

Feel free to try these applications and let us know if you have any suggestions.

 

Free ZIP Code Demographics, Address Correction and ZIP+4 Lookup Tools on CDXTech.com

We now have three tools on our website that complement the capabilities of our software, Excel add-ins CDXZipStream and CDXStreamer, and the web service CDXGeoData.  In addition to the ZIP Code lookup facility discussed in our previous post, you can also access the following free new tools:

1.  Address Correction and ZIP+4 Lookup

Need to find the ZIP or ZIP+4 Code for an address, or need to check an address for accuracy?  You can use the USPS postal service website, but our tool will not only provide the full address using standard abbreviations and formatting (like the USPs), but will also provide additional information like time zone and congressional district, and break the address into its separate components (like street, state, ZIP) for easy copying and pasting.  You can also enter the address in either single or multi-line format.  Here's a short tutorial showing how it works:

   

The website tool is convenient for looking up single addresses, but for cases where you have a long list of addresses that need correction and/or ZIP and ZIP+4 lookup, consider using our CDXStreamer Excel add-in or CDXGeoData web service.  We also provide free templates in both cases that completely automate the lookup prcess for you; please refer to the following postings for more information:

    CDXStreamer - A New Template to Verify and Parse Address Lists

    CDXGeoData  - Free Address Correction and ZIP+4 Lookup Template

2.  Demographic Data

Demographic data by ZIP Code is an invaluable resource for business planning and marketing.  The U.S. Census Bureau provides an exhaustive range of data from the decennial census as well as the annual American Community Survey.  We've compiled much of the key data into an easy-to-use tool where you can simply select the type of report desired, enter the ZIP Code of interest, and generate a report covering topics such as population, age, gender, income, race, housing value, educational attainment, and more.  This short video demonstrates how to use it:

   

For cases where you need demographics for a list of ZIP Codes, either our Excel add-in CDXZipStream or the CDXGeoData web service can provide bulk data quickly and easily.  CDXZipStream has up to fourteen data feeds for geographies ranging from census tract to state, while CDXGeoData focuses on ZIP Code demographics.  For more information on these options, please see the posts below:

    CDXZipStream - ZIP Code Demographics from the 2010 Census

    CDXGeoData    - Apps and Web Service for Location Analysis

 For your convenience, consider bookmarking all our website tools using the following links:

- Address Correction and ZIP+4 Lookup

- Demographic Data

- ZIP Code Lookup