(Requires license for PC*MILER Spreadsheets add-on. Contact us to learn more.)

Use Excel's Insert Function option to easily call PC*MILER functions and insert your arguments. 

The Miles function returns the driving distance from the origin to the destination.

=Miles (origin, destination, [routing type])

The origin and destination may be designated as one or more of the following:

  • A city with a state abbreviation (e.g. "Paris, TX").
  • A full street address (e.g. kingston, nj; 16 laurel avenue). (A license for the Streets add-on is required.)
  • A postal code. Three-digit U.S. ZIP codes representing centralized areas are acceptable only with a state abbreviation, (e.g. 123, NY).
  • A SPLC. (Add-on license required.)
  • A latitude/longitude point, with or without a street address.
  • A Canadian Postal Code. (Add-on license required.)
  • A custom place created in PC*MILER.  

Miles returns -1 if the origin, destination, or routing type is not valid.

TIP:  Leading zeros are significant in postal codes in many countries. For that reason, we recommend that you always enter or import numeric postal codes as text strings (format cells as “Text”). If you do enter or import a postal code as an integer, be aware that Microsoft Excel will strip off leading zeros before that number gets to PC*MILER.

Setting a Route Type

Setting a route type is optional. The default value is Practical. 

Options are: Prac (Practical), Short (Shortest), Natl (State + National Network), Toll (Toll Discouraged), and Air (Air Distance – this route type is not available in the PC*MILER user interface). Route type entry is not case sensitive (e.g. “TOLL” or “Toll” is valid), but each route option needs quotes around it.

The default routing type is determined by the default route profile setting in the PC*MILER user interface or settings in the PCMSERVE.INI file.

Duplicate Names

The PC*MILER database contains several thousand cities and towns that share the same name. If there are multiple instances of the city name you enter, the Miles function will match to the first instance of the city name it finds as it searches the database. For this reason:

  • You may want to enter postal codes for the origin and destination rather than city names whenever possible. Duplicate cities or towns that do not have a postal code assigned by the Postal Service are differentiated by the names of the counties in which they are located.
  • You may want to use the CityName or FullName function to see the full PC*MILER spelling and the postal code for a place name.

For Streets Add-on

When stops are city names or postal codes, by default “Highway Only” routing is used. This routing uses an air distance from the midpoint of the highway segment nearest to the destination postal code or city/state. Routes to stops that include a street-level address are calculated to the highway-level road closest to the address. 

You can change this default option in the PCMSERVE.INI file so that your route stays on local roads for as long as possible as it approaches a destination.

Set [Options] UseStreets=TRUE.  

To see an example of acceptable formatting for street-level addresses, see the Other Formulas tab of the DEMO_NA.xlsx workbook.

Miles Function Examples

TIP: Notice in the above example that quotes are needed around the Canadian postal code “K7L 1B5” due to the space after “L”. Spaces must be entered where they exist in postal codes and quotes must be used when entering a formula manually.

Video has no sound.