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