For a general overview of how to insert data and functions into your worksheet, please refer to the topic Inserting Data and Functions.
CDXRouteMP is a right-click function available in the MapPoint, Basic and Premium versions of CDXZipStream. Using Microsoft MapPoint, the function automatically calculates the driving distance, driving time, directions or cost between two or more 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 and cost can be optimized for specific driver profiles with MapPoint Settings options. A Trip Summary feature lists key results and settings; it also embeds a route map in a single step. Note that direct insertion of a custom function works with all returned data except maps.
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.
MapPoint has been discontinued by Microsoft as of December 2014, although it continues to be offered by third-party software resellers.
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 "07869") 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 "08033") or the worksheet cell location of the ZIP Code ("C5").
Route Option: Choose Preferred Route, Shortest Route (based on distance), or Quickest Route (based on time). The preferred route option will take into account the driver settings specified in the "Route OPtions" dialog described below.
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 about 547 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 ...)
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.
Please see the MapPoint Settings help topic for more information.
You can use CDXRouteMP to optimize complex routes of 4 waypoints or more. The "Optimize Route" options in the settings dialog must be checked. Note that route optimization using MapPoint assumes a fixed end point, where the optimized route both begins and ends with the same locations as the original waypoint list.
The Trip Summary feature is a convenient way to get access to all results in a single step. Reports can be created in miles or kilometers. The summary includes driving distance, time, duration,cost and a summary of the settings used to create the report.