Using our Microsoft Excel add-in, CDXZipStream, it’s easy to find the zip codes within a radius distance of a target zip.  Just right-click on a worksheet cell, and using the function CDXRadius, input the target zip code and specified radius distance;  an array or text string listing the zip codes within the radius will be returned to the worksheet.  However, this only applies to a straight-line distance around the target.  What if you need zip codes within a specified driving distance or time?

CDXZipStream does not have a special function for this type of calculation, but you can use a combination of two functions, CDXRadius and CDXRouteMP, to easily get the desired result.  For example, let’s say we’re looking for the zip codes within a 20-mile driving distance of zip code 07869.  (We can also do this for driving time as well – we’ll talk about that case later in this article.)  The first step is to find the zip codes within a somewhat larger straight-line radius, using CDXRadius  For this example we’ll use 25 miles.  This will capture all the zip codes within the 20-mile driving radius, and then some.  So the input for CDXRadius is:

Once we have the returned list of zip codes from CDXRadius, we’ll use CDXRouteMP to calculate the driving distance between each of these zips and our target zip.  Again, we right-click on the worksheet, select CDXRouteMP, and use this following input to calculate the driving distance to 07869:

Note that the first returned zip code from CDXRadius (other than the target zip in the first row) is in worksheet cell A2.  We use this as the second address in the calculation, so when we copy the resulting formula it will apply to all zip codes in each row.  The formula we obtain from this is:

=CDXRouteMP(1,0,"07869",A2)

We can copy this formula all along the list in Column C to get all the driving distances for all zip codes.  Columns A and B show the zip codes and their straight-line distances from the target, as calculated by CDXRadius, and colums C is the calculated driving distance from CDXRouteMP.   The first few rows are shown here.

Now just use Excel’s sort or auto-filter to find zip codes within a 20-mile driving distance.  When using auto-filter, select a “Number Filter” of “Less than” 20 for column C, and only those rows with distances of 20 miles or less will be visible in the worksheet.

What if you want to find a radius based on driving time?  The process is very similar, but when using the CDXRadius function assume that the vehicle is travelling 90 miles an hour (or 1.5 miles per minute) to obtain the initial list of zip codes.  For a 30-minute driving time, this means the specified (straight-line) radius distance would be 1.5 x 30 or 45 miles.  You can then narrow down the list obtained from CDXRadius by using the CDXRouteMP function to calculate driving time:

As we did before, sort or auto-filter to obtain only those zip codes within a 30-minute driving time.

If you would like a map of the result, use CDXRadius to output a drive time map to the worksheet:

To see short tutorials about the functions CDXRouteMP and CDXDistance, please refer to the following:

Find Zip Codes in a Radius Using Excel

Driving Distance Calculator in Excel

There are currently eight versions of CDXZipStream, our Microsoft Excel add-in for zip code, address, and route analysis.  Due to the broad spectrum of functionality of CDXZipStream, we’ve purposely created these versions to allow our customers to pick and choose the one that best fits their needs.  With the release of CDXZipStream 11.0 late last year we’d like to provide in today’s blog an updated review of the functionality of each version, to help you decide which one is right for you.  You can also refer to this reference table for a shorthand comparison.

Note:  We also offer a Canadian postal code database which is compatible with most CDXZipStream functionality.  Please refer to this article for more information about accessing Canadian data with CDXZipstream, or our FAQ page.)

Here are the eight versions and what they can do, along with relevant video tutorials:

FindZip:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)

Reference:  Zip Code Finder in Excel

Lite:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation) as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip code, and find the closest zip code to a target zip. 

Reference:  Find Zip Codes in a Radius

MapPoint:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation), but also has all the mapping and routing functions provided by Microsoft MapPoint, including creation of radius, location, drive time, and route maps, calculating driving time and distance, and route optimization.  It can also geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between two points of latitude and longitude.  In general, this CDXZipStream version is best suited for those requiring driving distance calculations, route optimization, and/or latitude and longitude determination.  Please note that CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel

Basic:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  CDXZipStream Basic is primarily used by those who require both extensive zip code functionality as well as driving and routing calculations supplied by MapPoint.

CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel, Geocoder in Excel

The next four versions also include demographic data.  For a detailed description of the data available in these versions, please refer to data fields information.

Population:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Population counts from the 2000 Census and current population estimates are also covered.

Demographics:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.

Reference:  Demographic Data in Excel

Premium:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Premium ACS:  Contains all the functionality and data of the Premium version, plus demographic data from the American Community Survey.  Covers basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.  Finally, 5-year aggregated data from the annual American Community Survey is provided in data feeds by city (place), county, state, and Core Based Statistical Area (CBSA).   Note that the ACS is administered by the U.S. Census Bureau, and replaces the old long form that was phased out after the 2000 Census.  It contains earnings data and other social and economic information that are not currently covered by the 10-year census. 

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Census Records in Microsoft Excel

Our address and zip code analyzer, CDXZipStream, includes a function called CDXRouteMP that performs a wide variety of tasks involving driving calculations, including route optimization.  We’d like to summarize here the variety of output information CDXRouteMP currently provides since it’s grown to be quite a substantial list that not all of our users may be familiar with.

First, let’s take a quick look at all the route calculation choices available through the input box.  This can be accessed by right-clicking on any cell in your Excel worksheet, then selecting CDXRouteMP from the CDXZipStream functions list:

Route calculations based on the shortest and quickest driving route options will result in the shortest driving distance and quickest driving time, respectively.  Calculations based on the preferred route will take into account preferred road types (such as highways, toll roads, arterial roads), which you can specify under the Route Settings Road Types tab.  Also see our post Using Microsoft MapPoint Settings with CDXZipStream for more information.

One more item worth noting:  If you are optimizing a route, the order of the intermediate stops is always optimized based on achieving the quickest driving time.  However, all the other output, such as driving time, distance, trip duration, and cost will be based on the selected route option:  shortest, quickest, or preferred.   Driving directions from waypoint to waypoint will also be based on the selected route option, although the order of the waypoints will again be based on the quickest driving route.

Over the last several months we've been developing Microsoft Excel templates that use CDXZipStream to perform a variety of tasks, ranging from route optimization to zip code radius analysis.  They are pre-formatted and automated so that users simply input their data, click a button or two, and the desired output is returned to the worksheet. The templates are free, work in Microsoft Excel 2003 and up, and can be evaluated with free trials of CDXZipStream and Microsoft MapPoint, which is also used in some of the templates.  (See the top of our links page to download.) At this point there are enough templates that we thought it would be a good idea to review in one place all their functionality and provide links to their YouTube tutorials:

Radius Calculations Template:  Input a list of addresses or zip codes, and this template will identify which ones are within a specified radius distance of a target zip code.  The calculations are based on the straight-line distance between the centroid (center) locations of the zip codes.  This is a one of our more popular templates and can be used for a variety of applications, such as filtering customer addresses around a store or other key location.  You can view the tutorial here.

Nuclear Facility Radius Calculator:  An example application of our Radius Calculations template that identifies nuclear facilities in a radius around a target zip code.   You can view the tutorial here.

Driving Distance Template:  This template works with Microsoft MapPoint to calculate driving time, driving distance, cost, or total trip duration between two lists of addresses. Input the same address for one of the lists if you need driving data around a single location.  This is a very easy way to validate travel expenses.  Please view the tutorial here.

Route Optimization Template (with GPX export):  This template also works with Microsoft MapPoint, to re-order and optimize intermediate stops on a driving route to achieve the shortest possible driving time.  The optimized route can then be exported to a GPX file that is compatible with many GPS devices.  This is a very easy-to-use, economical approach to route optimization that can reduce transportation costs for applications ranging from food delivery services to customer sales calls.  You can view the tutorial here.

Driving Matrix Template:  Input a matrix of addresses or zip codes to calculate driving distance, driving time, cost or trip duration between all combinations of routes.  For instance, the input matrix would look something like this:

Driving calculations will be performed for the route between Address 1 and Address A, Address 1 and Address B, Address 1 and Address C, etc., until all combinations are covered.  This is a quick and easy way to assign driving destinations, such as for delivery, taxi, or limousine services.  You can view the tutorial here.   

Geocoding Template:  Get latitude and longitude data for a long list of addresses.  This template works with Microsoft MapPoint, and can also be used to verify the accuracy of mailing lists based on the quality of the address match to the MapPoint database.   View the tutorial here.   

Reverse Geocoding Template:  Working with Microsoft MapPoint, this template reverse geocodes by finding the closest address to a geocode (latitude and longitude) point. This can be extremely useful for processing points from a gps device.  You can view the tutorial here.   

Although these templates cover many uses and will fit the bill for most clients, please contact us at customsolutions@CDXTech.com if you have a special need and require template customization. 

The CDXZipStream function CDXRouteMP is used to calculate driving distance, time, cost, and total trip duration for two or more stops on a driving route.  It can also optimize a route by reordering intermediate stops on the route in order to achieve the shortest driving time.  Please see our YouTube videos on route optimization and driving calculations for more information.

CDXRouteMP works in conjunction with Microsoft MapPoint, and it's important to make sure that MapPoint settings properly reflect the requirements of the route so the calculated values and route order (when optimizing) are accurate.  It's easy to set MapPoint settings using the Route Settings button on the main dialog box, shown below:

The main dialog box can also be accessed, just by right-clicking on a worksheet cell, in CDXZipStream free templates that are downloadable from our links page.  (This applies to the templates related to driving calculations which employ Microsoft MapPoint.)

After pressing Route Settings, the following dialog appears:

Each tab corresponds to a particular area of interest: driving speeds, fuel, driving costs, road types, and schedule.  There are also some general data to the right which are always visible regardless of the selected tab.

The data on the right covers route optimization and output preferences.  If more than four stops are part of the specified address range being analyzed, you can specify whether the stops will be optimized, i.e. the intermediate stops will be placed in the order that results in the shortest driving time.  Note that route optimization assumes that the first and last stops listed will remain constant; their order in the list will remain unchanged.  If the requested output is a trip summary, you can also request to include a map of all locations and a list of the waypoints according to their order in the driving route.  For output that includes directions and waypoint lists, the lists can be provided either as an array formula (where each value occupies its own worksheet cell), or as a text string in one single worksheet cell.

Note that the order of the stops when performing route optimization is always based on achieving the quickest driving time.  However, if the route type as specified on the main dialog box is the preferred or shortest (distance), then these selections will be accounted for when calculating the actual route directions, driving distance, cost, etc.  The order of the stops will not be affected by these selections, however. 

The driving speeds tab as shown above describes the preferred maximum driving speeds as a function of road type.  When specifying preferences for arterial roads, keep in mind that these are high capacity urban roads that are not quite as large as highways, and are designed to deliver traffic from collector roads to freeways, or between urban centers.

Fuel information on the second tab can impact both driving cost calculations and driving time, the latter particularly for long trips that require multiple stops for refueling:

 Driving costs are also impacted by the settings on the driving cost tab, which can be based on actual fuel consumption or a fixed price per mile:

The road types tab provides a qualitative measure of preferred roads.  This input only has an impact on driving calculations when the route type selected on the main dialog box is "preferred".  If the calculations are based on the "shortest" or "quickest" route, changing these settings will not impact the driving calculation results:

And finally, the schedule tab is useful when calculating total trip duration (including multi-day trips), where it is necessary to account for limitations on daily driving time, required rest periods, and time spent at each stop:

Whenever you perform driving calculations, we recommend that you quickly review the route settings described here to ensure they reflect the driving conditions you expect.  It can also useful to vary the settings to explore how even minor changes to your driving habits may impact cost and time savings.  For instance, estimating the cost savings provided by optimized routing, fuel efficient vehicles, or changes in driving schedules is easy to do by adjusting these settings, and can ultimately provide a good impetus for change.

CDXZipStream, our Microsot Excel add-in, has some very useful functions that perform a variety of location-based calculations, ranging from creating zip code lists for a given city to finding latitude and longitude for an address.  These functions are readily accessible by right-clicking with the mouse on any cell in an Excel worksheet - a "custom function" (similar to standard Excel functions like SUM or AVERAGE) is then inserted into the worksheet cell and performs the desired calculation.  Please refer to our previous blog for more general information about custom functions in Excel.

To help guide CDXZipStream users when accessing these functions, here is a quick review that includes short video tutorials (in Shockwave and Windows Media formats) on their use:

CDXFindZip:  Finds all the zip codes for a given city.  CDXFindZip.swf (1.56 mb)  CDXFindZip.wmv (1.03 mb)

CDXDistance:  Finds the distance between two zip codes.  CDXDistance.swf (1.88 mb)  CDXDistance.wmv (1.15 mb)

CDXRadius:  Finds all zip codes within a radius distance of a target zip code.  When used with Microsoft MapPoint, it can also create a radius map around a target zip code.  CDXRadius.swf (4.41 mb)  CDXRadius.wmv (3.10 mb)

CDXRouteMP:  Working with Microsoft MapPoint, calculates driving time, distance, and cost between two locations, and can also create a map of the route.  CDXRouteMP.swf (5.73 mb)  CDXRouteMP.wmv (3.77 mb)

CDXLocateMP:  Working with Microsoft MapPoint, verifies latitude, longitude, and address information for a given location, and can also create a map of the location.  CDXLocateMP.swf (4.24 mb)  CDXLocateMP.wmv (3.06 mb)

CDXDistance2WP:  Working with Microsoft MapPoint, calculates the distance between two waypoints.  A waypoint is a location defined by its latitude and longitude.  CDXDistance2WP.swf (2.27 mb)  CDXDistance2WP.wmv (1.47 mb)

CDXClosest: Finds the zip code from a list that's closest to a target zip code.  CDXClosest.swf (4.10 mb)  CDXClosest.wmv (2.70 mb)

CDXZipList:  Creates a list of zip codes for any city, county, or state.  CDXZipList.swf (3.08 mb)  CDXZipList.wmv (1.82 mb)

In upcoming posts we'll be reviewing these functions in more detail, showing how they can be used to solve real-world problems when dealing with zip code, address and location-based data in general.  Stay tuned!

CDXZipStream supports a variety of distance formulas including CDXDistance, CDXRouteMP and CDXDistance2WP.  Each is optimized for different situations and involves varying times to calculate.  

CDXDistance is used to calculate straight-line distance between zip codes.  You simply reference two zip codes and the CDXDistance formula returns the distance in either miles or kilometers.  The custom formula calls our custom database to return latitude and longitude for the zip code, and the distance calculation is performed based on this data.  It is relatively fast and should be used where the central location of a zip code can be used to approximate distance between locations.

If you need driving distance or time you will need to use CDXRouteMP, which works in conjunction with the desktop version of Microsoft MapPoint.  The calculation times for this are relatively slow at about 1 second per route, with more complex routes taking more time.  If you are going to make thousands of calculations you should dedicate your PC to this during off hours or run this on a separate machine.

The fastest calculation is CDXDistance2WP, which uses latitude/longitude pairs to perform straight line distance calculation. You can geocode a list of addresses and then use this function to calculate exact distances.  We recommend this when trying to analyze long address lists.

A typical task may require calculating driving time or distance for a large matrix of addresses or zip codes.  For instance, it may be necessary to determine which customers in a list of thousands are within a one hour driving time of multiple store locations.  The best way to handle this is a two-step process.  First, we can get the latitude and longitude of each customer address and use CDXDistance2WP to calculate the straight-line distance from each store.  Then we can filter this list for customers that are within 100 miles of each store, and use CDXRouteMP to get the exact driving time for this smaller list.  Using a combination of distance calculations in this case allows for fairly fast analysis of very large data sets, without sacrificing accuracy