Distance and Time Calculations
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 https://check-distance.com/ 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.