In previous articles we've covered how Microsoft Excel "custom functions" can be used to carry out many zip code analysis tasks. This includes formulas related to address and location-based information, such as importing demographic data and checking address accuracy. In combination with Bing Maps, even calculating driving time and performing route optimization is possible.
Custom functions, also referred to as User Defined Functions (UDFs), perform complex calculations or tasks and are used in cell formulas just like standard Excel functions such as SUM or AVERAGE. In this article we will discuss how custom functions can also be used to identify zip codes within a specified distance or "radius".
Zip codes can be extremely useful for analyzing demographic information, and can help you better target customers and plan marketing campaigns. For example, identifying zips within a specified radius of potential new store locations can help you select a location most accessible to your customer base. But instead of drawing circles on a map, a much easier approach is to use a custom function within Excel that can do the work for you.
Let's say you are evaluating a possible store location in zip 90210 and need a list of all zips within a 10 mile radius. With our CDXZipStream addin for Excel, a function named CDXRADIUS is available to automate this task. Simply select the cell location where you want the radius report and enter the formula "= CDXRADIUS (ZipCode, RadiusDistance)". For this case the formula is "= CDXRADIUS ("90210", 10)", and the worksheet will display all results within 10 miles of 90210. CDXZipStream also has a "right-click" function available to further automate this function.
The formula can be tailored to output the data as a text string listed in a single cell, or as an array with individual results in multiple cells in the spreadsheet. To return a formula as an array in Excel, simply highlight the destination range with your mouse, then hold down the Ctrl and Shift keys and press Enter. The returned data can also include the calculated distance and sort these by closeness to the target zip. Additional options can be added to have the distance returned in kilometers, miles or nautical miles.
The custom function automates the process of finding all zips that fall within the given radius by searching a local database and performing distance calculations based on latitude and longitude. All of these actions occur in the background, so the user works entirely within Excel and sees only the resulting list of zip codes displayed in their worksheet - there is no need to learn a new application or import a zip code list from a web page.
The video below shows how this all works:
Find Zip Code in a Radius using ExcelA big advantage of custom functions is their ability to process large amounts of data in a spreadsheet format. To perform zip code radius calculations for a list of zip codes in column A, for example, simply input the appropriate custom function formula in cell B1, such as: "= CDXRADIUS (A1, 10)" where "A1" is the worksheet cell location of the first zip code. Then just copy and paste this formula as needed in column B; in this way you can perform radius calculations for literally thousands of zip codes, without the time-consuming manual input required for other programs.
Custom functions can also perform more advanced radius analysis by address. Working with Bing Maps, it is possible to geocode each address and then calculate the distance to a central address. One example of this is calculating the distance each employee has to travel to a central business location. It is even possible to do a bulk geographic analysis to calculate the distances for a variety of central locations, For instance, this method could be used to select from a list of new possible office locations for your business.
Once your radius analysis is complete, new functions can be added to the list to identify income, population or other demographic variables by zip code. The list can then be sorted or filtered to show the optimal zipcodes. This can help you identify marketing opportunities for your business.
CDXZipStream, our Excel add-in that provides zip code data, demographics, and more is an ideal and cost effective choice for radius analysis. A free 30-day trial is available as a download from our website, as well as example spreadsheets and tutorials showing how to use custom functions in a variety of applications..