Get Driving Distance, Time and Cost in an Excel Template

Driving calculations such as driving time, distance, and cost can help support your business or organization in a variety of applications, such as:

 -Verifying expense reports for employee travel distance

-Determining the impact of driving time to treatment centers on patient compliance

-Assigning customer service calls (based on driving time)

-Setting pricing (based on driving cost)

Our Microsoft Excel add-in CDXZipstream, working in conjunction with Microsoft MapPoint, can perform these calculations for a large number of locations, all while working within the familiar Excel environment.  We also offer a free (downloadable) Excel template that further automates the process; just copy and paste address information into the template, specify the type of route and route calculation you need, and the requested data are automatically returned to the worksheet.  See how it works in the following short tutorial:

This can also be viewed on Youtube: Driving Distance and Time Calculations in an Excel Template

The template allows you to specify that the calculations will be based on the quickest, shortest, or preferred route type.   Preferred routes take into account road type, speed, and fuel cost settings that can be accessed by right-clicking on the worksheet (as shown in the video), or by clicking on the Application Settings icon on the CDXZipStream toolbar.  Then just press the Route Settings button with your cursor:

Another feature recently added to the template is the ability to globally set the country location of the addresses.  If you don’t want to include country information as part of address information, use the drop down box at the top of the “Driving Calculations” worksheet to select the country of choice.  This selection will also override the default country setting in MapPoint.  However, for any addresses that fall outside of this selection, you will need to specify the correct country in columns F and K (or as part of a single-line address in columns B and G.)  In MapPoint for Europe, only those countries that have detailed map data and address find capability are covered.  These are: Austria, Belgium, Denmark, Finland, France, Germany, Greece, Italy, Luxembourg, Netherlands, Norway, Portugal, Spain, Sweden, Switzerland, and the United Kingdom.  Only the U.S. and Canada are covered when using MapPoint for North America.

Regardless of the country selection, it is not necessary to have two unique address lists for the template.  If you are performing driving calculations for a list of addresses to a central location, just copy the address of the central location as the second address list in columns G through K (or just column G if it’s in a single line format).

Once the desired data is returned to the template, you can then use Excel’s sort or filter functions to find address pairs based on shortest distance, for instance, or travel times that span a specified range.  Please see this Microsoft tutorial for more information on how to use these functions.

Add comment