Microsoft Excel “custom functions” can be utilized to hold out many duties associated to deal with and zip-code primarily based info, similar to importing demographic knowledge, checking tackle accuracy, even figuring out zip codes inside a radius. Some of those are mentioned within the EzineArticle “Using Microsoft Excel to Manage Mailing Lists” by my colleague, Ian Roberts. Custom features, additionally known as User Defined Functions (UDFs), carry out difficult calculations or duties and are utilized in cell formulation similar to the usual Excel features SUM, AVERAGE, LOOKUP, and so forth. In this text we’ll evaluate how customized features can be used for calculating driving distance and driving time between addresses listed in an Excel worksheet.
Let’s say you’ve lists of addresses in Excel that require evaluation primarily based on driving distance, driving time, or each. Such an evaluation might assist a wide range of functions – to estimate delivery prices for your corporation, optimize supply service routes, even assist plan gross sales calls. You are most likely conversant in internet-based mapping companies similar to MapQuest or Google Maps the place you enter begin and finish factors to acquire driving instructions, mileage, and estimated driving time. This works high quality for a single pair of addresses, however for bigger units of knowledge an automatic strategy is required.
A customized operate working at the side of a mapping program like Microsoft MapPoint can calculate driving distance or time for varied route preferences (similar to shortest distance or quickest driving time) and routinely return the outcome to your Excel worksheet. It is not essential to study a brand new software, since all interactions with MapPoint occur within the background; you’re employed solely inside the acquainted Excel atmosphere. For instance, to calculate driving time between addresses listed in worksheet cells A1 and B1, merely enter the suitable customized operate components (inserted in cell C1, for example) which might look one thing like this: “= CustomFunction (A1, B1)”. If you’ve a number of pairs of addresses in columns A and B, simply copy and paste this components as wanted in column C – on this approach you’ll be able to routinely get hold of driving distance or time for actually hundreds of units of addresses, with out the time-consuming handbook enter required for typical mapping applications.
This kind of operate also can calculate routes with specified stopping factors alongside the best way, to simulate a real-life supply route, for instance. In this case, simply listing the addresses based on their order on the route, in a customized operate components similar to “= CustomFunction (Address 1, Address 2, Address 3, etc.)”. To optimize the route, you’ll be able to change the tackle order to see the impact on driving distance or time.
In conditions the place precise addresses should not accessible, customized features also can return driving time or distance utilizing extra common addresses primarily based on avenue identify, metropolis, or zip code. The route calculation makes use of the geographic heart of the given tackle. Address varieties don’t must be constant inside a single customized operate components. Examples of legitimate addresses are: “20015” “Louisville, KY” “Washington Street 02121”.
To sum up, this is a wonderful instance of how customized features in Excel can faucet into the facility of different applications, similar to Microsoft MapPoint, whereas permitting the consumer to work inside the acquainted Excel atmosphere. From checking the accuracy of mailing lists to calculating driving distance and time, it is simple to see how customized features might be precious instruments for analyzing tackle info in Excel.