CDXRouteMaptitude is a optional right-click function available for purchase in all versions of CDXZipStream™. With the Caliper Maptitude software installed (separate purchase), the function automatically calculates the driving distance and driving time between two or more addresses. It can also perform route optimization for up to 200 locations at a time, A Trip Summary feature lists key results and settings. Currently, only the US version of Maptitude is supported for Maptitude 2014 and greater.
CDXRouteMaptitude 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.) However, we highly recommend that to speed up data retrieval and minimize the number of Maptitude data requests, you enable the AutoCopy option and set the returned data to text when obtaining data for lists of locations. Autocopy is discussed in more detail below.
Before using CDXRouteMaptitude, you must install Maptitude which must be on the same PC as CDXZipStream. To get a free demo of Maptitude please use the following link.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXRouteBing 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 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. An Excel named range can also be used here.
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: Shortest Route (based on distance), 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 a Trip Summary report in miles or kilometers.
AutoCopy: By enabling AutoCopy, data will automatically be returned for an entire list of locations, starting at the specified worksheet cell and ending at the first occurence of an empty row. By using the "Set To Text" option, the data is returned as text instead of custom formulas. This allows for sorting, moving or other manipulation of the returned data while avoiding inadvertent data requests to Maptitude Maps. Route maps and Trip Summaries cannot be returned when using Autocopy.
Maptitude Maps Settings: Click on the "Maptitude Maps Settings" button to edit settings for Maptitude driving and routing calculations. Please see the topics Maptitude Maps Settings and Set Maptitude Maps Key for more information.
Acceptable address formats are:
Zip Code Example: 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 major discrepency in the address information (the ZIP Code is incorrect for the city or state provided) an error will be returned for that address.
Here is an example where the CDXRouteMaptitude function is applied to lists of addresses:
We are requesting that driving distance in miles be returned between two lists of addresses (ZIP Codes in this case) in columns B and C, starting in row 3. Alternatively, the range "B3:C43 could have been used as the Address 1 input, with Address 2 left emplty. AutoCopy is enabled to return data for the entire list, and the returned data is "Set to Text", which is recommended for long lists. The result is:
Note that the driving distance data in column D is returned as text and not formulas.
If there are more than two columns of addresses, i.e. each route has intermediate stops, the input for Address 1 can be a range, such as "B3:E3".
To use CDXRouteMaptitude function in a custom function formula, use the following format:
=CDXRouteMaptitude(Route Option, Route Calculation, Start Address, End Address)
Use the values below for the first two parameters:
Route maps cannot be returned through custom formulas or when using the AutoCopy option.
Here is an example using a custom function formula to return the "Quickest" "Distance in Kilometers":
To specify the route in terms of multiple "waypoints" (where the route consist of a start point, destination, and one or more stops along the way) use this generalized formula:
=CDXRouteMaptitude(Route Option, Route Calculation, Waypoint1, Waypoint 2, Waypoint 3, ...)
You can also specify a range of waypoints:
=CDXRouteMaptitude(Route Option, Route Calculation, WayPoint Range)
The following is an example of calculating driving distance in miles for a route through New York City. The waypoints are addresses listed in cells B4 through B7:
Road preferences and other settings can be specified for route calculations by using the "Maptitude Settings" button on the CDXRouteMaptitude right-click dialog:
Please see the Maptitude Settings help topic for more information.
You can use CDXRouteMaptitude to optimize complex routes of up to 200 locations at a time. The "Optimize Routes" option in the settings dialog must be checked, as shown above.
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 travel distance, duration and waypoint list. A map can also be optionally added.
