In today’s blog we’re going to highlight a neat little right-click function in CDXZipStream called CDXClosest Zip, which identifies which zip code out of a list is the closest distance (as the crow flies) to a target zip code.
CDXClosestZip is a fast, easy way to determine which store location is closest to a client, or which distribution center should be used for a product delivery. Just input the zip code list of interest into Microsoft Excel, along with the target zip code, and then right-click on any empty cell in the worksheet to select the CDXClosestZip function:
Let’s say we have a customer located in zip code 33607, and all store location zip codes to be searched are in the named range called ZipList. (As an alternative, we could also use a list range such as “C1:C100”.)
Here’s what the input would look like:
In this case we’re requesting that the closest zip code is returned to the worksheet – this would be the zip code in ZipList that is closest to 33607. As shown in the drop down list, we could also request the actual closest distance to the closest zip code in either miles, kilometers, or nautical miles.
CDXClosestZip can also be applied to a longer list of target customer zip codes. Just use the cell location of the first target zip code, like this:
The resulting custom function formula is returned to the worksheet cell:
= CDXClosestZip (B2,0,ZipList)
Where “B2” is the cell address of the first target zip, “0” indicates we’re requesting the closest zip code is returned to the worksheet, and “ZipList” is the named range of the zip code list that will be searched.
Once this result is returned to the worksheet, all we need to do is copy this formula along each customer zip code. For a short tutorial showing CDXClosestZip in action, please refer to the video below:
or the YouTube video Zip Code Distance Function in Microsoft Excel.
Keep in mind that CDXClosestZip is a relatively complex function. It calculates the distance for every zip code combination in both the targeted and searched lists. If the targeted and searched lists are both 1000 zip codes long, one million (1000 x 1000) distance calculations must be performed, along with subsequent sorting and displaying of results.
For very large lists, we do recommend an alternative approach (available with our CDXZipstream MapPoint and Basic versions) that works in conjunction with Microsoft MapPoint. In this case, an Excel matrix is set up for each zip code combination, like this:
Using the function CDXLocateMP, the latitude and longitude of each zip code location is found (this process is called geocoding), then the function CDXDistance2WP uses this data to calculate the distance between each location. This approach is faster than CDXClosestZip since no database calls to get the zip code locations need be made once the geocoding is completed. It’s then very easy to use Excel’s sorting function to find the closest distance to each target zip. For more information, you can download a spreadsheet that applies this approach to a real-life car dealership location analysis.