Driving Distance and Time Calculations Utilizing Microsoft Excel and MapPoint

 Microsoft Excel "customized capabilities" can be utilized to hold out many duties associated to handle and zip-code based mostly info, equivalent to importing demographic information, checking deal with accuracy, even figuring out zip codes inside a radius. A few of these are mentioned within the EzineArticle "Utilizing Microsoft Excel to Handle Mailing Lists" by my colleague, Ian Roberts. Customized capabilities, additionally known as Consumer Outlined Features (UDFs), carry out difficult calculations or duties and are utilized in cell formulation similar to the usual Excel capabilities SUM, AVERAGE, LOOKUP, and so on. On this article we are going to evaluation how customized capabilities can be used for calculating driving distance and driving time between addresses listed in an Excel worksheet.

To illustrate you could have lists of addresses in Excel that require evaluation based mostly on driving distance, driving time, or each. Such an evaluation may assist quite a lot of functions - to estimate delivery prices for what you are promoting, optimize supply service routes, even assist plan gross sales calls. You're most likely acquainted with internet-based mapping providers equivalent to MapQuest or Google Maps the place you enter begin and finish factors to acquire driving instructions, mileage, and estimated driving time. This works superb for a single pair of addresses, however for bigger units of knowledge an automatic method is required.

A customized perform working along with a mapping program like Microsoft MapPoint can calculate driving distance or time for numerous route preferences (equivalent to shortest distance or quickest driving time) and routinely return the consequence to your Excel worksheet. It is not essential to study a brand new utility, since all interactions with MapPoint occur within the background; you're employed solely throughout the acquainted Excel setting. For instance, to calculate driving time between addresses listed in worksheet cells A1 and B1, merely enter the suitable customized perform method (inserted in cell C1, as an example) which might look one thing like this: "= CustomFunction (A1, B1)". In case you have a number of pairs of addresses in columns A and B, simply copy and paste this method as wanted in column C - on this method you'll be able to routinely receive driving distance or time for actually 1000's of units of addresses, with out the time-consuming guide enter required for typical mapping packages.

This sort of perform may also calculate routes with specified stopping factors alongside the way in which, to simulate a real-life supply route, for instance. On this case, simply record the addresses in keeping with their order on the route, in a customized perform method equivalent to "= CustomFunction (Tackle 1, Tackle 2, Tackle 3, and so on.)". To optimize the route, you'll be able to change the deal with order to see the impact on driving distance or time.

In conditions the place precise addresses should not out there, customized capabilities may also return driving time or distance utilizing extra basic addresses based mostly on road title, metropolis, or zip code. The route calculation makes use of the geographic middle of the given deal with. Tackle varieties don't should be constant inside a single customized perform method. Examples of legitimate addresses are: "20015" "Louisville, KY" "Washington Road 02121".

To sum up, this is a superb instance of how customized capabilities in Excel can faucet into the ability of different packages, equivalent to Microsoft MapPoint, whereas permitting the person to work throughout the acquainted Excel setting. From checking the accuracy of mailing lists to calculating driving distance and time, it is easy to see how customized capabilities may be worthwhile instruments for analyzing deal with info in Excel.