How does our Microsoft Excel add-in, CDXZipStream, determine the straight-line distance between two points?  This question applies to the following right-click functions:

  CDXDistance - calculates the distance between two zip codes

  CDXDistance2WP - calculates the distance between two points of latitude and longitude, also referred to as waypoints

  CDXClosestZip – determines which zip code out of a list is closest to a target location 

(For calculating driving distance, please refer to our function CDXRouteMP, shown in the Youtube tutorial Driving Distance Calculator in Excel. )

Technically, it’s not a straight line distance in these cases, since we need to take into account the fact that the earth is (approximately) spherical in shape.  To do this, both CDXDistance and CDXDistance2WP use a mathematical equation for the shortest distance along a spherical surface, called the Great Circle formula:

d = a cos^-1[cosA1 CosA2 cos(B1 – B2) + sin A1 SinA2]

Where

d = the shortest path between two points on a sphere, also called the orthodrome

a = the radius of the earth, which is 3958.73926185 miles

A1, A2 = the latitude of points 1 and 2

B1, B2 = the longitude of points 1 and 2

For the functions CDXDistance and CDXClosestZip, which both require zip codes as input, the centroid location of each zip code area is used as the basis for the latitude and longitude points.

Note that this is an approximation of the distance, since the earth is not a perfect sphere (it's actually a lumpy ellipsoid), and changes in altitude along the earth’s surface will also impact the actual distance.  When the latitude and longitude are provided in decimal format, this equation can be converted into the following Excel-friendly formula:

d = 3958.73926185*((2*ASIN(SQRT((SIN((RADIANS(A1)-RADIANS(A2))/2)^2)+  COS(RADIANS(A1))*COS(RADIANS(A2))*(SIN((RADIANS(B1)-RADIANS(B2))/2)^2)))))

For those of you ready to unleash your inner geek, please see the Wolfram Mathworld website for more information about the Great Circle formula.

To see up close how CDXZipStream uses the Great Circle formula, refer to our Youtube tutorial Zip Code Distance Function in Microsoft Excel.

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.

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.