For a general overview of how to insert data and functions into your worksheet, please refer to the topic Inserting Data and Functions.
CDXRouteHere is a right-click function available in Here compattible versions of CDXZipStream. Using the Here API web service, the function automatically calculates the driving distance, driving time, and directions between two addresses.
CDXRouteHere 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 Here 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 CDXRouteHere, you must enter a HERE API key in CDXZipStream. A free developer key can be obtained at developer.here.com. Higher usage paid keys are available at here.com.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXRouteHere 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.
Travel Type: Select the travel type of interest: Driving, Walking, or Trucking.
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 Here.
HERE API Settings: Click on this button to edit settings for Here calculations and input a Here API key.
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
Latitude, Longitude example: 40.84|-74.56
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 an example where the CDXRouteHere 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 CDXRouteHere function in a custom function formula, use the following format:
=CDXRouteHere(Route Option, Route Calculation, Travel type, Start Address, End Address)
Use the values below for the first three parameters:
Here is an example using a custom function formula to return the "Quickest" "Distance in Kilometers" for the "Walking" travel type:
The CDXRouteHere calculation my take to 3 requests from the Here API web service. The API route call requires latitude and longtude for each waypoint rather than a text address. So 2 calls may be made to the Here API to geocode the waypoints. If you have this data you can enter address in the format "Latitude|Longitude", as an example "40.84|-74.56". If you are using zip codes, CDXZipStream will attempt to gecode the zip using the local zip code database before using the HERE API service.
You can use the Route Calculation option "WayPoints" to return latitude and longitude coordinates from full or partial addresses and postal codes.
The following is example output for a route request bewteen "405 Lexington Avenue, New Yorker 10174" and "1980 Murray Ave, Pittsburgh, PA" using the Route Calculation option "Directions in miles".
Directions can be retrieved in both miles and kilometers.
The CDXRouteHere function can also return routed mapping results as displayed for the three zip codes WayPoints below: