CDXRouteMP is a right-click function available in the MapPoint, Basic and Premium versions of CDXZipStream. Using Microsoft MapPoint® (North American and/or European versions), the function automatically calculates the driving distance, driving time, directions or cost between addresses as well as optimizes complex routes. 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.) Driving time can cost can be optimized for specific driver profiles with the Route Settings function. Route Maps can also be embedded in your spreadsheet using the CDXRouteMP right-click function. A Trip Summary feature is now available listing key results and setting as well as embedding a route map in a single step.
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 2013 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 or the range name containing all the stops in your trip. You may use the actual zip code (such as "06830") or the worksheet cell location of the zip code ("C4"), or the range of locations ("C4:C5") in Excel.
Address 2: The end point of the trip. This is only required when you only specify one address in the first address field. 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) or any of the other available options. This is the resulting output that will display in the worksheet. You can also specify to embed a route map created with MapPoint into an Excel worksheet or create a Trip Summary report in miles or kilometers.
In addition to specifying an Excel range acceptable address formats 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. If you are doing a multi-country analysis you should also add either "U.S." or "Canada" after the text.
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, ...)
you can also specify a range of waypoints as follows:
=CDXRouteMP(Route Option, Route Calculation, WayPoint Range Name ...)
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:
Route Driving Time and Driving Cost can use specific driver profiles that take into account items such as speed, fuel cost and other options. To set these parameters, using the "Route Settings" button on the "CDXRouteMP" right-click dialog shown below.
CDXZipStream can now optimize complex routes of 4 stops or more. The "Optimize Route" options in the settings dialog must be checked. The MapPoint optimization routine will minimize the times between specified stop and then CDXZipStream will apply the chosen route option between stops.
The Trip Summary feature is a convenient way to get access to all result items in a single step. Reports can be created in miles or km. The summary includes driving distance, time, duration,cost and a summary of the settings used to create the report.
