Website design By BotEap.comMicrosoft Excel’s “custom functions” can be used to perform many tasks related to address and postal code-based information, such as importing demographic data, verifying address accuracy, and even identifying postal codes within a radius. Some of these are discussed in the ezine article “Using Microsoft Excel to Manage Mailing Lists” by my colleague, Ian Roberts. Custom functions, also called user-defined functions (UDFs), perform complicated calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, LOOKUP, and so on. In this article, we will review how custom functions can also be used to calculate the driving distance and time between addresses listed in an Excel spreadsheet.

Website design By BotEap.comLet’s say you have lists of addresses in Excel that require analysis based on driving distance, driving time, or both. Such analysis could support a variety of purposes: estimating shipping costs for your business, optimizing delivery service routes, and even helping plan sales calls. You’re probably familiar with Internet-based mapping services like MapQuest or Google Maps, where you enter start and end points to get driving directions, mileage, and estimated driving time. This works fine for a single address pair, but for larger data sets an automated approach is needed.

Website design By BotEap.comA custom function that works in conjunction with a mapping program such as Microsoft MapPoint can calculate the distance or driving time for various route preferences (such as shortest distance or fastest driving time) and automatically return the result to your sheet Excel calculation. There’s no need to learn a new application, as all interactions with MapPoint happen in the background; you only work in the familiar environment of Excel. For example, to calculate the driving time between the addresses listed in cells A1 and B1 of the worksheet, simply enter the appropriate custom function formula (inserted in cell C1, for example) that would look like this: “= CustomFunction(A1, B1)”. If you have multiple pairs of addresses in columns A and B, simply copy and paste this formula as needed into column C; This way you can automatically get the distance or driving time for literally thousands of address sets, without the time-consuming manual. necessary input for typical mapping programs.

Website design By BotEap.comThis type of function can also calculate routes with specific stopping points along the way, to simulate a real-life delivery route, for example. In this case, simply list the addresses according to their order in the route, in a custom function formula such as “=CustomFunction(Address 1, Address 2, Address 3, etc.)”. To optimize your route, you can change the order of the directions to see the effect on driving distance or time.

Website design By BotEap.comIn 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”.

Website design By BotEap.comIn short, this is a great example of how custom functions in Excel can harness the power of other programs, such as Microsoft MapPoint, while allowing the user to work in 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.