The CDXZipStream function CDXRouteMP is used to calculate driving distance, time, cost, and total trip duration for two or more stops on a driving route. It can also optimize a route by reordering intermediate stops on the route in order to achieve the shortest driving time.
CDXRouteMP works in conjunction with Microsoft MapPoint, and it's important to make sure that MapPoint settings properly reflect the requirements of the route so the calculated values and route order (when optimizing) are accurate. It's easy to set MapPoint settings using the Route Settings button on the main dialog box, shown below:
The main dialog box can also be accessed, just by right-clicking on a worksheet cell, in CDXZipStream free templates that are downloadable from our links page. (This applies to the templates related to driving calculations which employ Microsoft MapPoint.)
After pressing Route Settings, the following dialog appears:
Each tab corresponds to a particular area of interest: driving speeds, fuel, driving costs, road types, and schedule. There are also some general data to the right which are always visible regardless of the selected tab.
The data on the right covers route optimization and output preferences. If more than four stops are part of the specified address range being analyzed, you can specify whether the stops will be optimized, i.e. the intermediate stops will be placed in the order that results in the shortest driving time. Note that route optimization assumes that the first and last stops listed will remain constant; their order in the list will remain unchanged. If the requested output is a trip summary, you can also request to include a map of all locations and a list of the waypoints according to their order in the driving route. For output that includes directions and waypoint lists, the lists can be provided either as an array formula (where each value occupies its own worksheet cell), or as a text string in one single worksheet cell.
Note that the order of the stops when performing route optimization is always based on achieving the quickest driving time. However, if the route type as specified on the main dialog box is the preferred or shortest (distance), then these selections will be accounted for when calculating the actual route directions, driving distance, cost, etc. The order of the stops will not be affected by these selections, however.
The driving speeds tab as shown above describes the preferred maximum driving speeds as a function of road type. When specifying preferences for arterial roads, keep in mind that these are high capacity urban roads that are not quite as large as highways, and are designed to deliver traffic from collector roads to freeways, or between urban centers.
Fuel information on the second tab can impact both driving cost calculations and driving time, the latter particularly for long trips that require multiple stops for refueling:
Driving costs are also impacted by the settings on the driving cost tab, which can be based on actual fuel consumption or a fixed price per mile:
The road types tab provides a qualitative measure of preferred roads. This input only has an impact on driving calculations when the route type selected on the main dialog box is "preferred". If the calculations are based on the "shortest" or "quickest" route, changing these settings will not impact the driving calculation results:
And finally, the schedule tab is useful when calculating total trip duration (including multi-day trips), where it is necessary to account for limitations on daily driving time, required rest periods, and time spent at each stop:
Whenever you perform driving calculations, we recommend that you quickly review the route settings described here to ensure they reflect the driving conditions you expect. It can also useful to vary the settings to explore how even minor changes to your driving habits may impact cost and time savings. For instance, estimating the cost savings provided by optimized routing, fuel efficient vehicles, or changes in driving schedules is easy to do by adjusting these settings, and can ultimately provide a good impetus for change.
You can see a video demonstration on how to use CDXRouteMP settings here:
or view the YouTube version: Route Optimization with One Click