Our address and zip code analyzer, CDXZipStream, includes a function called CDXRouteMP that performs a wide variety of tasks involving driving calculations, including route optimization. We’d like to summarize here the variety of output information CDXRouteMP currently provides since it’s grown to be quite a substantial list that not all of our users may be familiar with. Please note, the latest version of our software supports Bing Maps with a function named CDXRouteBing in a similar fashion.
First, let’s take a quick look at all the route calculation choices available through the input box. This can be accessed by right-clicking on any cell in your Excel worksheet, then selecting CDXRouteMP from the CDXZipStream functions list:
Since CDXRouteMP calculations are performed working in conjunction with Microsoft MapPoint in the background, please note that they will be impacted by the route settings in MapPoint, which cover a spectrum of settings ranging from gas prices to road preferences. Please refer to our previous post
Using Microsoft MapPoint Settings with CDXZipStream for a more detailed discussion of route setting options.
Also note that if a range of four or more addresses are provided as input for Address 1, and the route settings option is set for route optimization, all the output will be applied to the optimized route.
The route calculation options are:
1.Distance (in miles or kilometers) This is the driving distance between Address 1 and Address 2, or if more than three or more addresses are provided (optimized or not) the distance between the first and last addresses in the route.
2.Driving Time (in minutes, hours, or days) This is the driving time between Address 1 and Address 2, or if three or more addresses are provided, the driving time between the first and last addresses in the route.
3.Driving Cost (in dollars) This is the driving cost between Address 1 and Address 2, or if three or more addresses are provided, the cost over the entire route. Route settings can allow this to be calculated according to fuel cost, or at a fixed rate per mile or kilometer.
4.Insert Route Map This option inserts a map of the entire driving route.
5.Directions (in miles or kilometers) These are step by step driving directions over the entire route, including the travelled distance for each step.
6.Waypoints A waypoint is simply a stop along the route. This option lists all the specified waypoints in the route, and is most useful for an optimized route that shows the order of optimization. When optimizing routes, Microsoft MapPoint assumes that both the first and last stops are fixed in the order; only the intermediate stops are optimized.
7.Trip Duration This is the total trip time from start to finish including any layovers for multi-day trips and specified wait periods for each stop (for example, to account for unloading a delivery). Please refer to the Route Settings Schedule tab for specifying the assumptions concerning stop and wait periods.
8.Trip Summary This is a comprehensive trip report including the more commonly requested outputs: driving distance, time, cost, trip duration, and waypoints list.
Beyond the options listed here, it’s important to understand the types of route options available and how they affect the route calculations:
Route calculations based on the shortest and quickest driving route options will result in the shortest driving distance and quickest driving time, respectively. Calculations based on the preferred route will take into account preferred road types (such as highways, toll roads, arterial roads), which you can specify under the Route Settings Road Types tab. Also see our post Using Microsoft MapPoint Settings with CDXZipStream for more information.
One more item worth noting: If you are optimizing a route, the order of the intermediate stops is always optimized based on achieving the quickest driving time. However, all the other output, such as driving time, distance, trip duration, and cost will be based on the selected route option: shortest, quickest, or preferred. Driving directions from waypoint to waypoint will also be based on the selected route option, although the order of the waypoints will again be based on the quickest driving route.
For a short tutorial on how to use CDXRouteMP, please see the following video:
or watch the YouTube version: Driving Distance Calculator in Excel