Our zip code and location analysis add-in for Microsoft Excel, CDXZipStream, can be combined with the computational power of Excel to do a variety of interesting things.  For instance, we’ve just recently introduced an easy-to-use bulk radius analysis template for Excel that can take a matrix of X and Y locations (these could be stores, customers, warehouses, delivery points, etc.) and create a comprehensive report showing which X locations are closest to all Y locations, or vice versa. 

Let’s say we have a list of customer addresses and would like to determine which store locations are within a 10 miles radius of each customer.  This could be used to support, for example, a direct mail campaign to alert customers to the closest stores in their area.  But in cases where there may thousands of customers and thousands of stores, determining the closest stores for every single customer is not a trivial calculation.

So how does the template work?  First we copy and paste all store and customer addresses into the Excel template where they can be verified using the address verification function of CDXZipStream (MapPoint version).  Just click a button in the template, and CDXZipStream automatically determines if each address can be matched to a location in Microsoft MapPoint running in the background.  If a match can be found,  CDXZipStream geocodes the location to find its latitude and longitude.   These points of latitude and longitude are used to calculate straight-line distances between all combinations of stores and customers.  So to obtain a report showing the closest stores within radius of each customer, all we need to do is specify the radius distance (in this case, 10 miles) , click one more button, and a listing of all customers, along with the store locations within 10 miles, is automatically created in a worksheet.

The template can work with either full addresses (street, city, state, zip code) or zip codes alone if full addresses are not available.  The user can also bypass the address verification step by inputting latitude and longitude data directly, if it is available from a gps or other source.  And if you need to do the reverse calculation, i.e. find the closest customers for all store locations, just reverse the order of the data input.

For a short tutorial about the template, please watch the YouTube Video Store Locator Analysis in Microsoft Excel.  For more information, contact us at sales@cdxtech.com or 1-877-CDX-TEC1 (1-877-239-8321).  For current CDXZipStream clients, you can login to your on-line account and select "Buy Data Updates" from the Product Management Area.  Select the Bulk Radius Template from the drop-down list and proceed through the purchasing process.  You must own the CDXZipStream MapPoint, Basic, or one of the premium demographic versions to see this as an option.

The CDXZipStream function CDXRadius can easily provide a list of zip codes around a target zip code, but there are often situations where a simple radius area is not enough.  Particularly when planning for geographic coverage areas for sales reps or for sales or marketing campaigns, a more complex analysis is required.  In this post we’ll cover cases where two radius calculations need to be performed.

Case 1:  Include only the overlap of two radius areas

Need to focus a marketing campaign to attract customers from a competing store?  In a situation where it’s important to concentrate advertising dollars in an overlapping geographic market, here’s how to do a radius analysis using CDXZipStream in conjunction with the Excel LOOKUP function.

Let’s say we have two competing stores located in zip codes 19026 and 19041.  Using the CDXZipStream function CDXRadius, we locate the zip codes within a 5 mile radius of each, and the result (including distance in miles from the target) is:

Now we just use the Excel function VLOOKUP to determine which zip codes in the second list exist in the first list.  The first equation using VLOOKUP would look something like this:

=VLOOKUP(D3,$A$3:$B$32,2,FALSE)

Where …

    D3 is the cell address of the first zip code in the second list (zip code 19041)

    $A$3:$B$32 is the range in the first list that is being searched

    2 is the second column in the search range; the value from that column will be returned if the search is successful

   False indicates an exact match must be found

Now we just copy this formula along all the zip codes in the second list, and the result is:

If the zip code exists in both lists, the VLOOKUP search will result in a numeric value; if not, #N/A is returned.  Now just use Excel’s sort or autofilter functions to identify all zip codes that did not return #N/A, and you’ve just found the overlapping zip codes within a five mile radius of each store.

Case 2:  Exclude one radius from another

 Let’s say you need to add technical service personnel but want to avoid already covered areas.  Using the example from above, if you were adding personnel to zip code 19041 but wanted to exclude those zip codes in the 19026 area, simply do the same analysis but in this case use Excel’s sort or autofilter functions to identify all zip codes that did return #N/A, as those are the ones that were not found in the 19026 area.  

If you'd like more information, please follow these links:

- Find Zip Codes in a Radius Using Excel (YouTube tutorial)

- Using Excel’s sorting feature in Excel 2003 or Excel 2007-2010

- Using Excel’s autofilter feature in Excel 2003 or Excel 2007-2010

In our last blog we reviewed all the free Microsoft Excel templates we offer for CDXZipStream, that can help you make tasks like route optimization, zip code radius analaysis,  and geocoding that much easier.  You also have the option to customize the appearance of these templates, particularly to help highlight specific data using Excel built-in functionality.  Let’s take a look at the Zip Code Radius Calculations template as an example.

This particular template includes optional data columns B through E that can be used for information like name, telephone number, or any other fields associated with the listed addresses.  You can fill in the appropriate field names at the top of the data columns, or if you don’t need to use these columns, just hide them.  To hide the columns In Excel 2003, select Format (Column) from the main toolbar, or in Excel 2007 and 2010 use Format (Visibility) from the Home tab.   Do not delete these columns, since this will change the location of the input data required for the analysis, and will make the template inoperable.

We also recommend that to ensure you don’t lose any data along the way, you should save the template under a new name both before and after making any major changes.  

Now let’s review a few methods to change the display of the template data worksheet.  Keep in mind that once these changes are made, you can then copy and paste the results to another blank workbook to permanently save them:

1.  Sort - Most users of Microsoft Excel have used the sort function at some point, and it can certainly be used here if you need to sort the input or output data in the worksheet.  After pressing “Calculate” in the Zip Code Radius Calculations template, the worksheet will already be autofiltered in order to show the addresses or zip codes that fall within the specified radius distance of the target.  But even after autofiltering, the addresses will be shown in the order in which they were entered into the worksheet.  If you would like to the addresses in order of their distance to the target zip code, for example, select Data (Sort) from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Sort from the Data tab.  From here you can sort by the appropriate column in either ascending or descending order.  For more information about sorting, check out these links for Excel 2003 and Excel 2007-2010.

2.  Autofilter – Autofiltering hides any rows that do not meet the filter criteria you specify.  For instance, you may want to show only address data for a particular city, zip, or other data field.  Since the Zip Code Radius Calculations template automatically autofilters the results based on the distance from the target zip code, you will need to first turn off and then turn on the autofilter if you would like to use this for some other filtering criteria.  You can also filter based on two data fields, for example, if you want to show only those addresses within the target radius that are in a single town.  To autofilter  make sure a cell in the white data area is selected, then select Data (Filter - Autofilter) from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Filter from the Data tab.  For more details about autofiltering, see the following links for Excel 2003 and Excel 2007-2010.

3.  Conditional Formatting – This approach usually uses color to highlight data of interest.   Just specify the font color, background color, or other format option using similar specifications to autofilter;  in this case however, rows are formatted as specified if they meet your criteria, not hidden if they don’t.   For instance, you can highlight address and/or distance values as desired, and just like in sorting or filtering, this can be done simultaneously for one or more data fields.  To apply conditional formatting, select Format, then Conditional Formatting from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Conditional Formatting from the Home tab.  For more information about conditional formatting, see these links for Excel 2003 or Excel 2007-2010.

However, please remember to retain and make visible the instructions worksheet in the template that includes the Hughes Financial Services copyright.

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. 

Our Excel add-in CDXZipStream can return zip code radius and list data in the form of an Microsoft Excel array. An array is a group of related values that each occupy its own worksheet cell in an Excel worksheet.  It's often desirable to use arrays in cases where the returned data is too large for single worksheet cell.  We're going to review here how you can apply CDXZipStream arrays to large data sets.  This information applies to the right-click functions CDXRadius and CDXZipList, both of which allow the user to specify arrays as the output.

Let's say we're interested in finding all the zip codes within a 100 mile radius of target zip code 08033.  We can use the right-click function CDXRadius, with the following input:

The returned data is in an array two columns wide, showing both the zip codes within the radius in the first column, along with its distance in miles from 08033 in the second column.  Here's what the first few rows of the array look like:

If you click on any individual cell within the array, the formula is: {=CDXRadius("08033",50)}.  The custom function formula is surrounded by "curly" brackets indicating it is part of an array.  Note that the array acts as a single entity, such that you can't change or delete only part of the array; the array must be deleted in its entirety.

But what if we need to find zip codes within a 50 miles radius for a list of target zip codes?  It's possible to copy and paste the array to easily apply it to more than one target zip – here's how:

1.  Input the first zip code in cell A1 of a worksheet.

 2.  Right-click on cell A2, and select "Insert CDXRadius Function".

 3.  Use these inputs:

     Zip Code: A1

     Radius: 1000 (this will be changed later)

     Distance Unit:  distance in miles

     Result Out:  Array Formula

     Maximum Rows: 10000

4.  When you click OK, an array will be returned for the first zip code.   We purposely made this an extra large array with a 1000 mile radius, so when we copy and paste it for other zip codes we will be sure to have an array large enough to capture all the returned zips.  

5.  Now with the first array highlighted, use Excel's find and replace function to replace all "1000" values with "50" (while leaving out the quotation marks). 

6.  This array can now be copied and pasted immediately below any zip code to find the list of zip codes within 50 miles. 

You may want to experiment a bit with the size of the first array, by changing the radius distance or the number of maximum rows.  Just make sure it is large enough to capture all the data you need for subsequent target zips.

You can copy and paste the array manually, or record an Excel macro that can automate this process for you.  To prevent Excel from slowing down due to the large number of array formulas, just remember to occasionally copy and "paste special" the arrays as values as you proceed.

Even beyond their use in CDXZipStream, arrays can be powerful calculational tools for your worksheeets. For more general information about using Excel arrays, please check out the Microsoft website.

Radius analysis based on zip codes can be an extremely easy way to perform a variety of tasks, such as identifying customers closest to a store location, assigning client calls to sales representatives, or designing a direct-mail campaign for a charitable event.

Our Excel add-in CDXZipStream uses the custom function CDXDistance to calculate the straight-line distance between zip codes.  (Since this is a very fast calculation, thousand of zip codes can be processed very quickly.)  To help our clients better use this functionality, we are now offering a free Microsoft Excel template that employs CDXDistance to automatically filter zip codes based on their distance from a target zip.  

Just enter a list of zip codes in the template, specify the "target" zip code of interest and the radius distance (in either miles or kilometers), and the template automatically filters the list and shows only those zip codes that fall within the radius distance.  Any rows that contain zip codes not within the radius are hidden by Excel's autofilter function. 

There are also several blank columns available for other data associated with the address information in the template, such as name or telephone number.  These fields are filtered along with the provided zip codes and other address data.  After filtering, the results can be copied and pasted to a new Excel workbook; any hidden rows will not be copied to the new workbook.

As an example of how to use the template, we also offer a customized version which can identify nuclear facilities within a radius distance of a specified zip code.  In this case, we have provided (in the data entry area) zip code information for each nuclear facility located in the U.S.  As in the generic template, just enter a target zip code and radius distance, and only the nuclear facilities that fall within the specified radius will be displayed.

Both templates can be evaluated with the free demo version of CDXZipStream, and for longer-term use will work with the CDXZipStream Lite version.

Distance calculations are put in use when estimating freight costs, identifying closest customers and for many other business purposes. One popular application is a radius analysis, using distance to determine the nearest customers in a list to a central location. For convenience, most calculations use distance between zip codes.

Calculation of the distance between zip codes is based upon latitude and longitude information for each zip code. This geocoding data is determined by calculating the centroid of boundaries of each zip code. You can think of a centroid as a balance point, a location near the center of the areas of a polygon that make up the zip code.

CDXZipStream and other software used for distance and radius analysis use this geocoding information to calculate a straight line distance to a chosen zip code. All possible combinations of zip codes are reviewed and then sorted in ascending distance to show the closest ones. This type of analysis is fine when trying to point a customer to your nearest location, and every location is in a separate zip code.

But you should be aware of the possible inaccuracies. The land area in many zip codes can be hundreds of square miles. In fact the largest one in Tonopah, Nevada is 5,496 square miles. So if a site is on the periphery of a zip code distance calculations can be many miles in error. If you would like to know the land area associated with your zip code you can find this in our demographic database, along with water area and hundred of other items.

So what do you do if you need a more precise analysis? The solution is to determine exact locations by address, city and state for all locations. With this latitude and longitude information you can then determine the true, not approximated distance. CDXZipStream has a straight line calculation, CDXDistance2WP, to calculation distance between locations. CDXRouteMP can be used to calculate driving distance between addresses. So even evaluating multiple sites in the same zip code is no longer a problem. We’ll go into more detail on doing this in a future article.