RouteMetrics Functions

All functions are namespaced under ROUTE. Type =ROUTE. in any cell to see them in IntelliSense.

Watch the tutorial video for a full walkthrough, or jump straight to a function below.

ROUTE.DISTANCE(origin, destination, [mode], [unit], [profile])

Trip distance between two locations. Returns the number of hours.

=ROUTE.DISTANCE("Boston, MA", "New York, NY")                  →  217.33
=ROUTE.DISTANCE("Boston, MA", "New York, NY", "driving", "km") →  349.76

ROUTE.TIME(origin, destination, [mode], [departure_time], [profile])

Trip duration in minutes, rounded to one decimal. Returns a single number.

=ROUTE.TIME("Boston, MA", "New York, NY")                 →  223.7
=ROUTE.TIME("Boston, MA", "New York, NY", "transit")      →  281.0
=ROUTE.TIME("Boston, MA", "New York, NY", "driving", A1)  →  247.3   (A1 = tomorrow 8:00 AM)

ROUTE.DIRECTIONS(origin, destination, [unit], [mode], [departure_time], [profile], [stops])

Turn-by-turn directions as a spilled column, with the distance of each step. Drop it into one cell and the steps fill the cells below.

=ROUTE.DIRECTIONS("Boston, MA", "New York, NY")
  →  1. Head west on I-90 W (12.4 mi)
     2. Continue onto I-84 W (41.6 mi)
     3. ...

ROUTE.URL(origin, destination, [mode])

A clickable Google Maps directions URL between two locations. Returns text. No API call — this function never consumes a search.

=ROUTE.URL("Boston, MA", "New York, NY")
  →  https://www.google.com/maps/dir/?api=1&origin=Boston%2C+MA&destination=New+York%2C+NY&travelmode=driving

ROUTE.STATEMILES(origin, destination, state, [mode], [profile], [stops])

Truck miles a single trip drives in one state or province — the number IFTA fuel-tax returns are built on. Returns a single number, so you can copy it down a column: put one trip per row and a column for each state you run. Returns 0 when the route never enters that state.

=ROUTE.STATEMILES("Pittsburgh, PA", "Cincinnati, OH", "OH", "truck")  →  231.1
=ROUTE.STATEMILES("Pittsburgh, PA", "Cincinnati, OH", "PA")           →  45.3

Truck miles come from HERE truck-legal routing and are estimates for planning and IFTA prep, not a certified mileage source.

ROUTE.IFTA(origins, destinations, [mode], [profile], [stops])

A per-state mileage summary across many trips, in one formula. Point it at your From and To columns and it spills a two-column table — every jurisdiction your trips crossed and the total miles in each, sorted by miles. Blank rows are skipped; one billed search per origin/destination pair. Because it spills, use it in a single cell — don't copy it down (each state column of a per-trip breakdown is what ROUTE.STATEMILES is for).

=ROUTE.IFTA(A2:A100, B2:B100, "truck")
  →  Jurisdiction   Miles
     OH             616.5
     IN             193.7
     PA             101.7

If some trips can't be calculated (a bad address, or your searches running out partway), the totals only cover the trips that worked — so the table ends with an “Incomplete: N of M trips” row telling you how many are missing. Fix those rows and recalculate before filing.

A “Check: N of M trips” row means something different: those miles are complete, but for those loads no route legal for your truck exists, so the mileage follows a restricted one. Run ROUTE.RESTRICTIONS on them to see why.

ROUTE.TOLLS(origin, destination, [mode], [profile], [stops])

Total toll cost for a truck lane, as a number you can add straight into a cost-per-mile column. Priced against the truck profile you set, so axle count and weight are reflected wherever the toll authority charges by them.

=ROUTE.TOLLS("Chicago, IL", "New York, NY", "truck")  →  92.30

Toll prices are best-effort: they vary by payment method, time of passage and vehicle class, and not every road is covered. When no price can be worked out for a lane, the cell says so rather than returning 0 — a free road and an unknown one are different answers, and only one of them belongs in a cost total.

ROUTE.RESTRICTIONS(origin, destination, [mode], [profile], [stops])

Whether a lane is legal for your truck. Returns "OK" when the route breaks no restrictions, or a description of what it breaks — a low bridge, a posted weight limit, a hazmat prohibition, a road closed to through traffic.

This is worth a column on any lane list. When no compliant route exists, the mileage functions still return a number, because a route was found — just not one your truck can legally run. This is the function that says so.

=ROUTE.RESTRICTIONS("Columbus, OH", "Chicago, IL", "truck")
  →  OK

=ROUTE.RESTRICTIONS(A2, B2, "truck", "height=14ft6")
  →  Route violates a vehicle restriction (size, weight, or hazmat)

Setting your truck

Travel mode "truck" on its own keeps you off roads and turns that are closed to trucks outright. It cannot check a low bridge, a posted weight limit or a hazmat prohibition, because those depend on the vehicle — and until you describe yours, routes are planned for a standard truck.

Open the RouteMetrics task pane and fill in the Your truck panel. It saves into the workbook, so it travels with the file and applies to every truck formula in it. For a one-off oversize load, pass a profile argument on that row instead.

gross=80000lb; height=13'6"; width=102; axles=5

Anything you leave out simply isn't checked. A profile RouteMetrics can't read fails the cell with an explanation rather than falling back to a default truck — a wrong route that looks right is the failure worth avoiding here.

Routing is an estimate. Confirm clearances, weight limits and permits against your own route survey before dispatching a load.

Accepted input formats

• Full addresses: "1600 Amphitheatre Pkwy, Mountain View, CA"
• City + state (most reliable for fuzzy input): "Austin, TX"
• Coordinates: "40.7128,-74.0060"
• Airport codes: "LAX", "JFK"
• Place names: "Eiffel Tower"

Tip: When fill-down accuracy matters, normalize to "City, ST" format.

Caching and freshness

Each cell that gets an answer uses one search. RouteMetrics then remembers that answer inside your workbook, so when Excel recalculates the sheet — on reopening the file, after deleting rows, or on a full recalculation (Ctrl+Alt+F9) — those routes are answered from the workbook itself. No request is made and no search is consumed.

Each cell you fill costs one search, including repeats of the same route — filling a route down twenty rows is twenty searches. What's free is asking again for a route this workbook has already answered: recalculations, reopening the file, and the same workbook being open in two places at once all reuse the stored answer rather than charging for it again.

Two limits worth knowing. A workbook remembers up to 3,000 recent routes; a sheet larger than that keeps its most recently calculated ones and may look the rest up again. And remembered answers refresh after 30 days, so a long-lived workbook doesn't quietly report distances from last year. Traffic-aware lookups (any call with a departure time) aren't remembered at all, because predicted traffic goes stale within hours.

How current are the results?

To keep things fast, we briefly remember each lookup on our servers and reuse it for repeat calls. How long we hold a result depends on how quickly that kind of value actually changes in the real world — distances between two fixed points barely move (held the full 30 days our map provider allows), while live traffic changes by the hour:

These windows govern our servers. Your workbook has its own 30-day memory on top of them, so a recalculation alone won't fetch a fresh answer for a route you already have — it will simply reuse it, free. To deliberately refresh a route, change one of its inputs (or wait out the 30 days), which sends a new lookup and draws one search.

Common errors

When a function can't complete, the cell shows a descriptive error message as text instead of the expected number / URL / list. The text starts with RouteMetrics: so you can spot it at a glance. Common messages:

Heads up: aggregating a column that contains an error message (e.g. =SUM(B2:B100)) returns #VALUE! for the total — which is the right behavior, since silently summing past an error would give you a wrong number.