CDXRouteMP is a right-click function available with both the basic and premium data feeds. Using Microsoft MapPoint®, the function automatically calculates the driving distance or driving time between two addresses. It can be employed as a right-click function as well as input directly into your worksheet as a custom function formula. (Please also refer to the help section on custom functions.)
Please refer to the Youtube video Driving Distance Calculator in Excel for a quick demonstration of the CDXRouteMP function.
Before using CDXRouteMP, you must install Microsoft MapPoint. CDXRouteMP is compatible with the 2002 through 2009 versions of MapPoint. A free downloadable trial of Microsoft MapPoint is available here. If your version of MapPoint includes a run CD, it must be inserted in your PC drive.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXRouteMP Function, and you will see the input box shown below:
The following inputs are required:
Address 1: The beginning point of the trip. You may use the actual zip code (such as "06830") or the worksheet cell location of the zip code ("C4").
Address 2: The end point of the trip. You may use the actual zip code (such as "06830") or the worksheet cell location of the zip code ("C4").
Route Option: Choose Preferred Route, Shortest Route (based on distance), or Quickest Route (based on time)
Route Calculation: Choose Distance (in miles or kilometers), or Driving Time (in minutes, hours, or days). This is the resulting output that will display in the worksheet.
In this case the cell contents "06830" have been automatically inserted as Address 1, but you can input any valid address as Address 1 or Address 2. Acceptable addresses are:
Zip Code Example: 06830
Street Zip Code Example: 101 Field Point Road 06830
City Zip Code Example: Greenwich 06830
City State Example: Greenwich CT
Street, City Zip Code Example: 101 Field Point Road, Greenwich 06830
Street, City, State Example: 101 Field Point Road, Greenwich, CT
Street, City, State Zip Code Example: 101 Field Point Road, Greenwich, CT 06830
Either the full state name or two-letter abbreviation can be used. If there is a discrepency in the address information (the zip code is incorrect for the city or state provided) or a misspelling, the returned output will be "Ambiguous or Invalid" for that address.
In cases where driving times or distances between lists of addresses must be calculated, input the CDXRouteMP custom function formula as shown below:
The CDXRouteMP function is input as a custom function formula in the following generalized form:
=CDXRouteMP(Route Option, Route Calculation, Address 1, Address 2)
In this case the first pair of addresses are in cells B4 and C4, and the custom function formula is:
=CDXRouteMP(2, 0, B4, C4)
The first two parameters in the formula represent settings for the Route Option and Route Calculation. The first parameter (for Route Option) is 2, representing the preferred route, and the second parameter (for Route Calculation) is 0, representing the distance in miles. Settings for Route Option and Route Calculation can be:
For this example, the calculated driving distance for the first pair of addresses is 556.00668 miles:
To apply this function to the rest of the data, copy and paste the custom function formula. In this case, we copy cell D4 and paste it to the range D5 through D13:
This cut and paste technique will also work if the zip codes are listed in rows instead of columns as shown here.
It is also possible to specify the route in terms of multiple "waypoints". The route will consist of a start point, destination, and one or more stops along the way. The generalized formula looks like this:
=CDXRouteMP(Route Option, Route Calculation, Waypoint1, Waypoint 2, Waypoint 3, ...)
The first waypoint is the start point and the last waypoint is the destination. The following is an example of the shortest route (in miles) starting in Washington D.C. and ending in Boston, by way of Philadelphia and New York. The waypoints are the addresses listed in cells B2 through B5:
