CDXZipStream, our Microsoft Excel add-in for zip code and address analysis, has a number of built-in functions that perform area calculations around a given geographic location. These can be invaluable tools for designating sales and service territories, finding customers for a brick-and-mortar store, designing delivery routes, and more. Here’s a quick review of this capability:
Generate a zip code radius list: The CDXZipStream function CDXRadius can quickly find zip codes within a specified radius of a central zip code. This is based on straight-line distance calculations between the centroid points of the target zip code and all nearby zip codes. To use CDXRadius, right-click on any cell in your Excel worksheet, and from the menu click on CDXZipStream Functions, then Insert CDXRadius Function. The input box will look like this:
After specifying the target zip and radius distance, you can ask for the resulting list of zip codes either as an Excel array formula (see here for more information on Excel arrays), or as a text string within a single worksheet cell. The output will also include distance data from the target zip, with the data separated by vertical bars when listed as a text string:
Find locations within a zip code radius: Do you have a long list of addresses or zip codes, and want to determine if they fall within a specified radius of a store or other location? It’s very easy to do this by cross-checking your address list against the list generated by CDXRadius. First, run CDXRadius with the desired target zip code and radius distance, making sure to specify the output as an array formula. Then for each location in your original list, use the Excel LOOKUP function to see if the address zip code exists in the array generated by CDXRadius. A step-by-step tutorial of this method is shown below, and in the YouTube video Find Zip Codes in a Radius Using Excel.
As an alternative, CDX Technologies also offers a free, downloadable Excel template that automatically identifies locations around a zip code. This template is especially well-suited for use with very long address lists, since the template code accesses CDXZipStream functions directly (versus using Excel worksheet functions) which increases calculational speed. Please refer to our links resource for CDXZipStream template downloads.
Find exact locations within a radius area: Sometimes zip codes are just not accurate enough, so when you need to use exact locations for a radius analysis, use a combination of two CDXZipStream functions. First use CDXLocateMP to find the latitude and longitude of each address, then CDXDistance2WP to calculate the straight-line distance between each latitude/longitude point and the central point of interest. The Excel autofilter feature can then quickly find the locations that meet the radius distance criteria. This multi-step process is explained in detail in our blog post How to Very Accurately Filter Adresses Based on Distance.
Map an Area: You can also use the CDXRadius function to map a radius around a zip code. Specify the target zip code and radius distance, and request the output as a radius map.
Need a drive time map that delineates the area within a specific driving time of a location? Specify the target zip and drive time, and request the output as a drive time map (using the CDXRadius function again). The drive time area is shown in red and is typically a polygon, reflecting variations in road conditions and driving speeds:
Geographic area analysis can be a critical component of activities ranging from target marketing to product delivery. Remember CDXZipStream when you need a fast and economical area analysis that meets your business needs.