Microsoft Excel’s “user-defined functions” can be used to perform many tasks related to address and zip code-based information, such as: Some of these are discussed in my colleague Ian Roberts’ ezine article, Using Microsoft Excel to Manage Mailing Lists. User-defined functions, also known 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 examine how user-defined functions do this also can be used to calculate the driving distance and driving time between addresses listed in an Excel worksheet.
Suppose you have lists of addresses in Excel that require analysis based on driving distance, driving time, or both. Such analysis can support a variety of purposes – to estimate shipping costs for your business, to optimize delivery routes, and even to help plan sales calls. You’re probably familiar with web-based mapping services like MapQuest or Google Maps, where you enter start and end points to get directions, mileage, and estimated travel time. This works well for a single pair of addresses, but an automated approach is required for larger datasets.
A custom function, working in conjunction with a mapping program such as Microsoft MapPoint, can calculate driving distance or time for various route preferences (e.g. shortest distance or fastest driving time) and automatically return the result to your Excel worksheet. There is no need to learn a new application as all interactions with MapPoint take place in the background; You only work in the familiar Excel environment. For example, to calculate the travel time between the addresses listed in worksheet cells A1 and B1, simply enter the appropriate custom function formula (e.g. inserted into cell C1), which would look something like this: “=CustomFunction(A1,B1) “. If you have multiple pairs of addresses in columns A and B, simply copy and paste this formula into column C as needed – this way you can automatically find driving distance or time for literally thousands of sets of addresses, without the time-consuming manual Input required by typical mapping programs.
Such a function can also calculate routes with fixed stopping points along the way, for example to simulate a real delivery route. In this case, just list the addresses according to their order on the route in a custom function formula like “=CustomFunction(Address 1, Address 2, Address 3, etc.)”. To optimize the route, you can change the address order to see the impact on driving distance or time.
In situations where exact addresses are not available, custom functions can also return drive time or distance using more generic addresses based on street name, city, or zip code. The route calculation uses the geographic center of the specified address. Address types do not have to be consistent within a single custom function formula. Examples of valid addresses are: “20015” “Louisville, KY” “Washington Street 02121”.
In summary, this is an excellent example of how Excel user-defined functions can harness the power of other programs such as Microsoft MapPoint while allowing the user to work in the familiar Excel environment. From checking mailing list accuracy to calculating driving distance and time, it’s easy to see how custom functions can be valuable tools for analyzing address information in Excel.
Thanks to Betty Hughes | #Driving #Distance #Time #Calculations #Microsoft #Excel #MapPoint
