Skip to main content

driving Distance and Time Calculations using Microsoft Excel and MapPoint


Microsoft Excel “custom functions” can be used to perform many duties associated with deal with and zip-code based totally facts, which include uploading demographic information, checking deal with accuracy, even figuring out zip codes within a radius. a number of these are mentioned inside the EzineArticle “using Microsoft Excel to manage Mailing Lists” via my colleague, Ian Roberts. custom functions, also referred to as consumer defined features (UDFs), carry out complicated calculations or responsibilities and are used in cellular formulation much like the standard Excel functions SUM, common, lookup, and so on. In this text we can evaluate how custom functions also can be used for calculating driving distance and driving time between addresses indexed in an Excel worksheet.

let’s assume you have lists of addresses in Excel that require evaluation primarily based on driving distance, driving time, or both. calcolo percorso Such an analysis could guide a diffusion of functions – to estimate delivery prices to your commercial enterprise, optimize shipping carrier routes, even assist plan sales calls. you’re possibly familiar with net-based totally mapping offerings such as MapQuest or Google Maps wherein you enter begin and quit factors to obtain riding instructions, mileage, and envisioned driving time. This works pleasant for a unmarried pair of addresses, but for larger sets of facts an automatic method is needed.

A custom feature working together with a mapping program like Microsoft MapPoint can calculate riding distance or time for various route options (which includes shortest distance or quickest driving time) and robotically go back the end result for your Excel worksheet. It isn’t always essential to analyze a brand new software, since all interactions with MapPoint occur inside the heritage; you figure most effective inside the familiar Excel environment. for instance, to calculate using time among addresses listed in worksheet cells A1 and B1, clearly input the precise custom function components (inserted in cell C1, as an instance) which might look some thing like this: “= CustomFunction (A1, B1)”. when you have multiple pairs of addresses in columns A and B, simply replica and paste this formulation as wished in column C – on this manner you can routinely obtain driving distance or time for literally hundreds of units of addresses, with out the time-ingesting manual input required for normal mapping applications.

This kind of function also can calculate routes with special stopping points alongside the way, to simulate a real-existence shipping route, as an example. In this case, simply listing the addresses in line with their order at the route, in a custom function formula consisting of “= CustomFunction (address 1, cope with 2, deal with three, and so forth.)”. To optimize the course, you may change the cope with order to see the impact on using distance or time.

In situations where genuine addresses aren’t available, custom features also can return riding time or distance the use of greater trendy addresses primarily based on street name, city, or zip code. The path calculation uses the geographic middle of the given deal with. deal with kinds do now not want to be regular inside a unmarried custom function formula. Examples of legitimate addresses are: “20015” “Louisville, KY” “Washington street 02121”.

To sum up, that is an terrific example of how custom features in Excel can faucet into the power of other programs, which include Microsoft MapPoint, at the same time as permitting the user to paintings inside the familiar Excel surroundings. From checking the accuracy of mailing lists to calculating using distance and time, it is smooth to peer how custom features can be precious equipment for studying cope with facts in Excel.

Leave a Reply

Your email address will not be published.