[ad_1]
Microsoft Excel “custom functions” can be used to carry out many tasks related to address and zip-code based information, such as importing demographic data, checking address accuracy, even identifying zip codes within a radius. Some of these are discussed in the EzineArticle “Using Microsoft Excel to Manage Mailing Lists” by my colleague, Ian Roberts. Custom functions, also referred to as User Defined Functions (UDFs), perform complicated calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, LOOKUP, etc. In this article we will review how custom functions can also be used for calculating driving distance and driving time between addresses listed in an Excel worksheet.
Let’s say you have lists of addresses in Excel that require analysis based on driving distance, driving time, or both. Such an analysis could support a variety of purposes – to estimate shipping costs for your business, optimize delivery service routes, even help plan sales calls. You are probably familiar with internet-based mapping services such as MapQuest or Google Maps where you input start and end points to obtain driving directions, mileage, and estimated driving time. This works fine for a single pair of addresses, but for larger sets of data an automated approach is needed.
A custom function working in conjunction with a mapping program like Microsoft MapPoint can calculate driving distance or time for various route preferences (such as shortest distance or quickest driving time) and automatically return the result to your Excel worksheet. It isn’t necessary to learn a new application, since all interactions with MapPoint happen in the background; you work only within the familiar Excel environment. For example, to calculate driving time between addresses listed in worksheet cells A1 and B1, simply input the appropriate custom function formula (inserted in cell C1, for instance) which would look something like this: “= CustomFunction (A1, B1)”. If you have multiple pairs of addresses in columns A and B, just copy and paste this formula as needed in column C – in this way you can automatically obtain driving distance or time for literally thousands of sets of addresses, without the time-consuming manual input required for typical mapping programs.
This type of function can also calculate routes with specified stopping points along the way, to simulate a real-life delivery route, for example. In this case, just list the addresses according to their order on the route, in a custom function formula such as “= CustomFunction (Address 1, Address 2, Address 3, etc.)”. To optimize the route, you can change the address order to see the effect on driving distance or time.
In situations where exact addresses are not available, custom functions can also return driving time or distance using more general addresses based on street name, city, or zip code. The route calculation uses the geographic center of the given address. Address types do not need to be consistent within a single custom function formula. Examples of valid addresses are: “20015” “Louisville, KY” “Washington Street 02121”.
To sum up, this is an excellent example of how custom functions in Excel can tap into the power of other programs, such as Microsoft MapPoint, while allowing the user to work within the familiar Excel environment. From checking the accuracy of mailing lists to calculating driving distance and time, it’s easy to see how custom functions can be valuable tools for analyzing address information in Excel.
[ad_2]
