CDXRouteBing is a right-click function available in Bing-compatible versions of CDXZipStream™. Using the Bing Maps web service, the function automatically calculates the driving distance, driving time, and directions between two or more addresses. It can also perform route optimization for up to 100 locations at a time, with either a fixed or non-fixed end point. A Trip Summary feature lists key results and settings; it also embeds a route map in a single step.
CDXRouteBing 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.) Direct insertion of a custom function works with all returned data except maps. However, we highly recommend that to speed up data retrieval and minimize the number of Bing data requests, you enable the AutoCopy option and set the returned data to text when obtaining data for lists of locations. Autcopy is discussed in more detail below.
Before using CDXRouteBing, you must enter a Bing Maps license key in CDXZipStream. Please see the Set Bing Maps Key topic for more information.
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), or Quickest with Traffic (considering traffic conditions)
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 by Bing into an Excel worksheet or create a Trip Summary report in miles or kilometers.
Travel Type: Select the travel type of interest: Driving, Walking, or Transit.
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 Bing Maps. Route maps and Trip Summaries cannot be returned when using Autocopy.
Bing Maps Settings: Click on the "Bing Maps Settings" button to edit settings for Bing driving and routing calculations. Also use this interface to set the Bing Maps key required for using CDXRouteBing and CDXLocateBing functions, and to track the number of Bing data requests made for the current Excel session. Please see the topics Bing Maps Settings and Set Bing Maps Key for more information.
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 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. If you are doing a multi-country analysis you should also add the country name as part of the address.
US addresses are shown here, but address formats vary by country. Use the standard address format for the country of interest.
Here is the map from the CDXRouteBing example above:
Here is an example where the CDXRouteBing 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 4. Alternatively, the range "B4:C4" 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 "B4:E4". Intermediate stops can only be applied to driving and walking routes.
To use CDXRouteBing function in a custom function formula, use the following format:
=CDXRouteBing(Route Option, Route Calculation, Travel type, Start Address, End Address)
Use the values below for the first three 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" for the "Transit" travel type:
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:
=CDXRouteBing(Route Option, Route Calculation, Travel type, Waypoint1, Waypoint 2, Waypoint 3, ...)
You can also specify a range of waypoints:
=CDXRouteBing(Route Option, Route Calculation, Travel type, 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 "Bing Maps Settings" button on the CDXRouteBing right-click dialog:
Please see the Bing Maps Settings help topic for more information.
You can use CDXRouteBing to optimize complex routes of up to 100 locations at a time, with either a fixed or non-fixed end point. 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, waypoint list and map:
