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

 

 

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.

 

Available from our links webpage (under Technical Articles) is an example spreadsheet showing how to use CDXZipStream geocoding and distance functions in an analysis of car dealership locations in the United States.  To download, just click on the link "CDXZipStream - Closing Chrysler Dealers Geocoding Analysis and Distance Matrix".  Let’s review how this works, and how you may be able to use this example in support of your own organization.

In this case there are over 700 dealerships, with locations throughout the United States, which are being considered for closing.  As part of this consideration it is necessary to calculate their relative proximity to each other.  Although it would be possible to calculate the driving distance between each location using the CDXZipStream function CDXRouteMP, a simpler and faster calculation is straight-line distance, based on the latitude and longitude of each dealership.  Since the additional accuracy of driving distance is not necessary for this analysis, we will be using straight-line distance here.

The first step, as shown in the worksheet “Dealers Closing” is to find the latitude and longitude of each dealer, either based on the exact address or by zip code.  The function CDXLocateMP is used to find the latitude and longitude in each case, such as in the following formula in columns I and J, for the first row of data:

=CDXLocateMP(1,D2,E2,F2,G2,"US")

Where

CDXLocateMP is the CDXZipStream custom function formula that finds latitude and longitude

1 indicates that latitude will be calculated and returned to the worksheet; 2 is used for longitude

D2, E2, F2, G2 are the cell addresses of the street, city, state and zip code of each dealership

“US” is the country

Since cell addresses are used in this equation, the equation can be copied down the list of dealerships to obtain latitude for all locations in all rows.  

In the first row of columns K and L the formula uses zip code only:

=CDXLocateMP(1,G2)

This is also copied down the enter list of dealerships.  In cases where MapPoint cannot locate the exact dealership address, only the zip code is used where the centroid of the zip code area is the basis for the geocoding calculation.

Columns M and N uses the Excel IF and ISNUMBER functions to select the latitude and longitude of the zip code centroid when the exact address is missing:

=IF(ISNUMBER(I2),I2,K2)

In the next worksheet, called Dealer Distance Matrix, the location and latitude and longitude data (from columns M and N) of the previous worksheet are placed into a matrix to calculate the distance between all combinations of dealerships.  Straight-line distance is calculated in this matrix using the function CDXDistance2WP.  In the uppermost left hand corner of the matrix, the following formula is used:

=CDXDistance2WP($C5,$D5,E$3,E$4)

Where

CDXDistance2WP is the CDXZipStream custom function formula that calculates distance between two sets of latitude and longitude

$C5, $D5, E$3, and E$4 are the cell addresses of the latitude and longitude pairs

The dollar sign before the column letter of row number in this equation indicates that this value does not change when the formula is copied to other areas of the worksheet.  The 789 x 789 matrix contains the distance in miles between all dealerships, across the range E5 through ADM793.  Due to the number of columns required for this example, Excel 2007 or 2010 must be used.  Excel 2003 can be used for matrices that have 256 columns or less.

The distance data ican now be effectively displayed in the worksheet “Closest Dealers” , showing dealerships according to their distance from a select dealer location.  Just use the drop down box at the top left hand side of the sheet to select a location, and all other dealerships are sorted and displayed by distance using Visual Basic for Applications code.  (Make sure that macros are enabled for this workbook, by clicking on the Macro Security icon from the Developer tab on the Excel ribbon, then select Enable All Macros.)  The VBA  code is accessible by clicking on the Visual Basic icon from the Developer tab and can be customized to suit your particular application, whether it be finding the closest competitor stores, assigning sales representatives, or targeting distribution locations by customer.

For more information about this type of location analysis, please see the following:

Geocoder in Excel (Video tutorial for the CDXLocateMP function)

How to Very Accurately Filter Addresses Based on Distance (CDX Technologies blog)

 

 

Address information is a very basic component of running a business, whether you’re defining a marketing plan based on yours customer’s geographic location or routing service calls for your technical team.  Address accuracy is also critical for minimizing marketing, customer service, and delivery costs. Our Microsoft Excel add-in CDXZipStream is great at helping you perform zip code and address analysis, but we want to highlight here another good option which tends to get overlooked:   geocode (latitude and longitude) information as an alternative to text addresses.

You may remember from third grade geography how the idea of using points of latitude and longitude on the globe started a long time ago in ancient Greece.   The use of this “geocode” system was pretty much limited to navigating the seas until the general use of GPS (Global Positioning Systems) via satellite exploded in the marketplace.  Now a Garmin GPS can get you to Grandma’s house on Thanksgiving Day, and your GPS-enabled iPhone can find your morning coffee at the nearest Starbucks.

The great thing about latitude and longitude data, as opposed to a text address, is that it is an unequivocal identification of a specific place on the map.  Streets get renamed, buildings get torn down and renumbered, zip code areas get added or redefined, but latitude and longitude does not change.  Particularly when address validity is questionable, i.e. the zip code doesn’t match the city, the house number doesn’t exist, or the street name is misspelled, you may want to use latitude and longitude instead.

CDXZipStream, working in conjunction with Microsoft MapPoint, can both geocode an address (find its latitude and longitude) and reverse-geocode (find the closest address for a latitude/longitude point).  It can also use latitude and longitude as input to functions like CDXRouteMP and CDXLocateMP.   For example, if we want to calculate the driving distance to a customer location with a questionable street address, we can use CDXRouteMP with latitude and longitude, like this:

This is equivalent to using the worksheet equation:

=CDXRouteMP(0,0,"752 W End Avenue, NY, NY 10025","40.789283|-73.966078")

Latitude and longitude is provided as “40.789283|-73.966078" where the values are separated by a vertical bar "|".  Just remember to use decimal format and negative values for west and south global locations.  All locations in North America will have positive latitude and negative longitude values, and all European locations will have positive latitude and positive longitude values.

To use latitude and longitude for the function CDXLocateMP, input the geocode data as shown below:

In this case, we are requesting street information for the geocoded point, input as a single-line address.  You can also input latitude and longitude separately as a multiline address, in the street and city input boxes, respectively.  Again, this is equivalent to the worksheet formula:

=CDXLocateMP(4,"40.789283|-73.966078",,,,,1)

Do you know the location exists, but the address can’t be found by Google or MapPoint?  You can find the latitude and longitude of the location in question, or a nearby landmark, by right-clicking on the point in Google Maps and selecting the option “What’s here?”  The latitude and longitude values will automatically show up in the Google search box near the top of the screen.  You can also use resources like Itouchmap.com or Microsoft MapPoint, which shows latitude and longitude of the pointer at the lower right-hand area of the displayed map.  Once you have latitude and longitude, input these values into the CDXRouteMP or CDXLocateMP functions.

Latitude and longitude is also very useful for calculating straight-line distance between locations.  You can use the CDXZipStream function CDXLocateMP to find latitude and longitude for a list of addresses, then use CDXDistance2WP to find the distance between all desired points.  This is a very fast, very accurate calculation.  (For a more detailed description of this process, please refer to our prior blog article “How to Very Accurately Filter Addresses Based on Distance“.)  Don’t have detailed address information?  CDXLocateMP can find latitude and longitude based solely on zip code.  You can then use these points to calculate distance using CDXDistance2WP.  In this case, CDXZipStream uses Microsoft MapPoint to find the latitude and longitude of the zip code centroids. The centroid is the weighted geographic center of each zip code area.  

We also provide preformatted Excel templates which can perform geocoding and reverse-geocoding.  Just cut and paste your address or zip code list into the template and get your data at the click of a button.  These are free and can be downloaded from our links page.

For some short tutorials related to using latitude and longitude with CDXZipStream, please see the following videos:

Get Latitude and Longitude for Addresses in Excel 

Reverse Geocode Template for Microsoft Excel 

Gecoder in Excel 

 

 

CDXZipStream, our Microsoft Excel add-in that performs zip code, address, and routing analysis, now offers a Canadian postal code database that works with a number of CDXZipStream functions.  Once you’ve purchased the Canadian data, here’s how it can be used:

(Note:  All of the functions here are accessed by right-clicking on any worksheet cell, and selecting “CDXZipStream functions” from the drop-down menu.  Then select the specific function you would like to insert into the worksheet.   You also have the option to manually input these functions directly into the worksheet cells, using the formulas illustrated below.)

CDXDistance finds the straight-line distance between any two zip codes or postal codes.  Just input the zip code or postal codes of interest (alternatively, you can input the worksheet cell addresses of the codes) and click OK. 

 

 

The resulting formula will be input to the worksheet:  =CDXDistance (“T0K 0A9”, “L4Y 1Z8”), and display the value 1576.836 miles.   If you use a cell address you can also copy the formula (e.g. =CDXDistance (A1,B1)) to a list of codes, to calculate distances between multiple pairs.

CDXFindZip is a lookup function for zip and postal codes.   Need to find all the zip codes for Vancouver, British Columbia?   If you’ve purchased the Canadian database, you have the option of selecting either a state or province in the first drop-down box:

 

After selecting the desired city and clicking OK, the resulting formula will be input to the worksheet:  =CDXFindZip("Vancouver","British Columbia"), and all the postal codes will be displayed as a long text string in the format “Code1 | Code2 | Code3 | … “  You can also manually input the formula into a worksheet cell, and if you use cell addresses (e.g. =CDXFindZip (A1, B1)) for the city and province pair, you can copy this formula to apply it to a long list of data.

For a short tutorial on how to use CDXFindZip, please view the video “Zip Code Finder in Excel.”

CDXZipList finds all the zip codes for a U.S. State or Canadian province, U.S. county, or city.  The output can be provided as a single text string (as in CDXFindZip) or an Excel array formula.  The associated city, county, state or province can also be specified as part of the output. 

For a short tutorial on how to use CDXZipList, please view the video “Zip Code Lists in Microsoft Excel.”

If you would like to see how to obtain an array output from CDXZipList for large sets of data, please see our blog “Applying CDXZipStream Arrays to Large Sets of Data.”

CDXRadius finds the list of zip or postal codes within a specified radius area of a target zip or postal code.  Just input the target code, specify the radius area and output options, and whether you want to search either the U.S. or Canadian databases, or both.

If you are working with codes that are close to the U.S./Canadian border, select the “US and Canada” option under the database dropdown list to capture both zip and postal codes within the radius area.  Keep in mind this will slow down the function a bit since it’s searching a much larger set of data;  you also have the option of selecting just “United State” or “Canada” to keep the radius analysis within the country of interest.

If you would like to see how to obtain an array output from CDXRadius for large sets of data, please see our blog “Applying CDXZipStream Arrays to Large Sets of Data.”

CDXClosestZip finds the closest zip or postal code to a target code, especially useful in cases where the closest store location for a customer must be found.  First, specify a customer code and then the Excel range containing the zip or postal codes for every store. 

CDXClosestZip then returns the nearest store code or the distance from the customer. 

You can copy the resulting formula down a long list of customers to determine the closest store for each.  For a short tutorial on how to use CDXClosestZip, please view the video “Zip Code Distance Function in Microsoft Excel.”

Also note, that since the CDXLocateMP and CDXRouteMP functions use the database available through Microsoft MapPoint, these are unaffected by the addition of the Canadian postal code database.  Since the North American version of MapPoint encompasses the U.S., Canada, and Mexico, the geocoding, address verification and routing capabilities of these functions will continue to cover these areas through the data available in MapPoint.

If you've ever hesitated with indecision when the dessert cart comes around, we know how you feel.  Choice is a good thing when it comes to the sweet things in life, but sometimes a little help is needed to make the right decision.  We especially believe that having a choice is a good thing when it comes to software, and so we've always offered CDXZipStream in several versions to provide our clients with the functionality that best suits their needs.  Now with eight different versions and a wide (and growing) range of capabilities, we thought it would be a good idea to review in detail the functionality of the most popular versions, as an aid to current and future clients as they purchase and upgrade their software. (Note:  Pricing information for both new purchases and upgrades is available here, with discounts available for multiple licenses.  And remember, all versions of CDXZipStream come with a 30-day money-back guarantee, so don't worry, you can send back that cherry tart after taking a bite ...)

CDXZipStream Lite 

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic). See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

CDXZipStream Basic 

Does everything the Lite version does, and contains additional functionality provided by Microsoft MapPoint.

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic).  See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Basic:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium

Does everything the Basic version does, and contains extensive demographic data.

- Provides basic demographic data by zip code, city, county, area code, CBSA, and state

  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA, CDXState, CDXCensus, and CDXCensus2.   See here for specific data fields for    these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium ACS

Does everything the Premium version does, and contains demographic data from the latest American Community Survey.

- Provides extensive demographic data by zip code, city, county, area code, CBSA, and state, from the American Community Survey, 2000 Census, latest Business Census,    and other sources.  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA,  CDXState, CDXCensus, CDXCensus2, CDXACSCBSA,    CDXACSCounty, CDXACSPlace, and CDXACSState   See here for specific data fields for these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium ACS:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

Sometimes it's particularly important to get accurate distance information between addresses, and when managing large address lists, the fastest and most accurate approach is to first geocode the locations (get their latitude and longitude) and then use this information as the basis of the distance calculations.  CDXZipStream does this particularly well with a combination of two functions, CDXLocateMP and CDXDistance2WP.  We're going to review step-by-step how you can use these two functions within Microsoft Excel, to filter an address list based on the calculated straight-line (as the crow flies) distance to a target address. 

Let's say you have a long customer address list and would like to determine which ones are closest to a specific store location.  In many cases, especially in sparsely populated areas where zip codes may cover hundreds of square miles, it's not appropriate to use the centroid location of the zip codes as the basis of the distance calculations, the approach used in many zip code-to-zip code calculators.  To get accurate address-to-address distances, first geocode all addresses using CDXLocateMP.  The input box would look like this, where Excel cell references are used as the input for each address.

This is a multi-line address where the street, city, state and zip code are listed in cells A2 through D2.  We also request that latitude is returned to the worksheet cell where the cursor is pointed, and here is the result returned to cell E2:

We repeat the process to get the longitude returned to cell F2, by simply specifying longitude as the returned data.  Now all we have to do is copy and paste the formulas in these cells to the rest of the list, and all customer addresses are now geocoded.

After also geocoding the location of the targeted store, we're now ready to calculate the actual distances to the store based on latitude and longitude, using the function CDXDistance2WP.  Since this function uses a straightforward mathematical formula, the calculations are very fast.  Here's an example of the CDXDistance2WP input:

We use cell addresses for the data, in this case the geocoded "waypoint".  For the target location, we also specify an unchanging cell location using the dollar sign ($) before the column letters and row numbers.

To get distance data for the entire list, we again just copy the resulting equation from the first row of data all along the list of geocoded points.  To see a short tutorial showing the use of both CDXLocate and CDXDistance2WP functions, please see the YouTube video Geocoder in Excel.

Now use Excel's sort capabability to sort the distances, or use the autofilter function for more advanced features.  Autofilter can easily show you all the distances that fall within a radius.  Let's say we want to narrow the list of addresses to those within a 50 mile radius of the target address.  In Excel 2007 and 2010, follow this procedure:

1. Use your cursor to select all the data (addresses and distances) you wish to filter.

2. From the Data tab, select Filter.

3. You should now see drop-down buttons at the top of each column of data.  (You should be using column headings here so the buttons don't cover the first row of data.) Click on the drop-down button in the column of distance data.

4. Click on "Number Filters"

5. Select "Less Than or Equal to"

6. Enter the value "50" in the box to the right of "Less than or Equal to"

All rows that do not meet the 50 mile or less requirement are now hidden.  You can now copy and paste the filtered data to a new workbook to save it.

For more information about autofiltering, see the following links for Excel 2003 and Excel 2007-2010.

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!

 

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.