Distance between two coordinates in Google Sheets (haversine formula)

The formula

Put the first point's latitude and longitude in A2 and B2, and the second point's in C2 and D2, all as decimal degrees (for example 34.0522 and -118.2437).

Miles:

=2*3958.8*ASIN(SQRT(SIN(RADIANS(C2-A2)/2)^2+COS(RADIANS(A2))*COS(RADIANS(C2))*SIN(RADIANS(D2-B2)/2)^2))

Kilometres: replace 3958.8 (Earth's mean radius in miles) with 6371.

=2*6371*ASIN(SQRT(SIN(RADIANS(C2-A2)/2)^2+COS(RADIANS(A2))*COS(RADIANS(C2))*SIN(RADIANS(D2-B2)/2)^2))

Check it with a known pair: Los Angeles (34.0522, −118.2437) to New York (40.7128, −74.0060) gives about 2,445.6 miles (3,935.7 km), and London (51.5074, −0.1278) to Paris (48.8566, 2.3522) gives about 343.6 km. We tested these formulas before publishing.

A whole column at once

To fill a whole column from one cell (E2), wrap it in ARRAYFORMULA and skip empty rows:

=ARRAYFORMULA(IF(A2:A="",,2*3958.8*ASIN(SQRT(SIN(RADIANS(C2:C-A2:A)/2)^2+COS(RADIANS(A2:A))*COS(RADIANS(C2:C))*SIN(RADIANS(D2:D-B2:B)/2)^2))))

Unlike geocoding formulas, this uses no API and no quota, so it's fine to let it recalculate.

When both coordinates are in one cell

Coordinates copied from Google Maps arrive as one piece of text, such as 34.0522, -118.2437. You don’t have to split them into columns first. With point A’s coordinates in A2 and point B’s in B2:

=LET(p, SPLIT(A2, ", "), q, SPLIT(B2, ", "), lat_a, INDEX(p, 1), lng_a, INDEX(p, 2), lat_b, INDEX(q, 1), lng_b, INDEX(q, 2), 2*3958.8*ASIN(SQRT(SIN(RADIANS(lat_b-lat_a)/2)^2+COS(RADIANS(lat_a))*COS(RADIANS(lat_b))*SIN(RADIANS(lng_b-lng_a)/2)^2)))

SPLIT(A2, ", ") splits at every comma and every space and drops the empty pieces, so it works with or without a space after the comma. LET gives the four numbers names so the haversine part stays readable. Don’t name them la1 or lo1: those look like cell references (column LA, row 1), and Sheets rejects them. With 34.0522, -118.2437 and 40.7128,-74.0060 the formula returns 2,445.6 miles, the same as the four-column version.

Make it reusable: a named function

Google Sheets lets you save a formula under your own name:

1. Data → Named functions → Add new function.
2. Name: HAVERSINE_MI. Argument placeholders: lat_a, lng_a, lat_b, lng_b. Placeholder names can't look like cell references, so avoid names like lat1.
3. Formula definition:

   =2*3958.8*ASIN(SQRT(SIN(RADIANS(lat_b-lat_a)/2)^2+COS(RADIANS(lat_a))*COS(RADIANS(lat_b))*SIN(RADIANS(lng_b-lng_a)/2)^2))

4. Now use =HAVERSINE_MI(A2, B2, C2, D2) anywhere in that spreadsheet. You can import named functions into other spreadsheets from the same menu.

Find the nearest location

A common use: for each customer, find the nearest store. With store names in Stores!A2:A100, their latitudes in Stores!B2:B100 and longitudes in Stores!C2:C100, and the customer's latitude and longitude in B2 and C2:

=INDEX(SORTN(Stores!A2:A100, 1, 0, ARRAYFORMULA(2*3958.8*ASIN(SQRT(SIN(RADIANS(Stores!B2:B100-B2)/2)^2+COS(RADIANS(B2))*COS(RADIANS(Stores!B2:B100))*SIN(RADIANS(Stores!C2:C100-C2)/2)^2))), TRUE), 1)

It calculates the distance from the customer to every store and keeps the closest one. Set the ranges to exactly your store rows (here rows 2 to 100), with no blank rows: a blank row counts as coordinates 0, 0. To get the distance itself instead of the name, wrap the same ARRAYFORMULA(…) part in MIN().

For the top three, everything within a radius and a drive-time check, see the full guide: how to find the nearest location in Google Sheets.

Distance between ZIP codes

ZIP codes aren't coordinates, so geocode them first (see how to geocode in Google Sheets; add the country, for example "62701, USA"). Then use the formula above. The result is the distance between the centers of the two ZIP areas, which can be several miles off for any specific address.

To skip geocoding, use free Census coordinates for every ZIP code: distance between ZIP codes in Google Sheets.

Convert degrees, minutes and seconds

GPS units, deeds and older lists often write coordinates as degrees, minutes and seconds, like 40°26'46"N. The distance formulas need decimal degrees. This formula converts one value in A2, whatever symbols it uses:

=LET(t, A2, n, SPLIT(REGEXREPLACE(t, "[^0-9.]+", " "), " "), v, INDEX(n, 1)+IFERROR(INDEX(n, 2), 0)/60+IFERROR(INDEX(n, 3), 0)/3600, IF(REGEXMATCH(t, "[SWsw]|^\s*-"), -v, v))

It replaces every run of non-digits with a space, splits what’s left into degrees, minutes and seconds, and adds them up as degrees + minutes/60 + seconds/3600. If the text contains S or W, or starts with a minus sign, the result is negative, because south latitudes and west longitudes are negative.

Put latitude and longitude in separate cells before converting; the formula reads one value at a time. Two things it can’t fix: a decimal comma (40,5) and a typographic minus sign (−) copied from a web page. Replace both first, for example with SUBSTITUTE(A2, "−", "-").

Kilometres, nautical miles, metres and feet

The only thing that changes between units is the Earth’s radius at the front of the formula (the 3958.8):

For short distances such as a delivery zone or a site survey, metres or feet are easier to read than 0.0142 miles. Round the result to a sensible precision with ROUND(…, 1); the formula itself is only accurate to about 0.5% (see How accurate is it?).

A distance table, and the length of a route

Every point to every other point. With names in A2:A5, latitudes in B2:B5 and longitudes in C2:C5, put =TRANSPOSE(A2:A5) in F1 for the column headings and this in F2:

=ARRAYFORMULA(ROUND(2*3958.8*ASIN(SQRT(SIN(RADIANS(TRANSPOSE(B2:B5)-B2:B5)/2)^2+COS(RADIANS(B2:B5))*COS(RADIANS(TRANSPOSE(B2:B5)))*SIN(RADIANS(TRANSPOSE(C2:C5)-C2:C5)/2)^2)), 1))

Comparing a column (B2:B5) with a row (TRANSPOSE(B2:B5)) makes Sheets calculate every pair, so one formula fills a 4 × 4 grid. Row 2 holds the distances from the first point, column F the distances to it, and the diagonal is 0. With Seattle, Portland, Spokane and Boise, Seattle to Boise comes out at 404.5 miles. The grid grows with the square of the list: 300 points is 90,000 cells, which is fine; 3,000 points is 9 million, close to the 10-million-cell limit of a whole spreadsheet. For big lists, use the nearest-location formulas instead.

Total length of a route with stops. With the stops in visiting order in rows 2 to 5, add up each leg (row 2 to 3, 3 to 4, 4 to 5) by offsetting the ranges by one row:

=SUMPRODUCT(2*3958.8*ASIN(SQRT(SIN(RADIANS(B3:B5-B2:B4)/2)^2+COS(RADIANS(B2:B4))*COS(RADIANS(B3:B5))*SIN(RADIANS(C3:C5-C2:C4)/2)^2)))

For Seattle → Portland → Spokane → Boise that’s 721 miles in straight lines. For a longer route, change both ranges together: the first runs from the second stop to the last, the second from the first stop to the one before the last.

How accurate is it?

• The haversine formula treats Earth as a sphere. Compared with the more precise ellipsoid calculations used in surveying, the error is at most about 0.5% (at worst around 12 miles on a 2,445-mile trip) and negligible for most business uses.
• It's the distance as the crow flies. Road distance is often 20% to 40% longer, and more around water and mountains. For real routes, see driving distance in Google Sheets.
• You may see a shorter formula with ACOS (the spherical law of cosines). It gives nearly the same answer, but haversine stays accurate for very short distances, so we recommend it.

Troubleshooting

#VALUE! or a parse error. At least one coordinate is text, not a number. Common causes are a degree sign, a trailing space, an N or W letter, or a column imported as plain text. Test with =ISNUMBER(A2): it should say TRUE. Fix the column with Format → Number → Number, or convert with =VALUE(TRIM(A2)), or use the degrees-minutes-seconds formula above.

Thousands of miles between places that are close together. Latitude and longitude are swapped, or a minus sign is missing. Every point in the Americas has a negative longitude, and every point south of the equator a negative latitude. This check flags values that can’t be right: =IF(AND(ISNUMBER(A2), ABS(A2)<=90, ISNUMBER(B2), ABS(B2)<=180), "ok", "check"). It catches a swap only when the longitude is bigger than 90, so also eyeball a few rows on a map.

Odd results in empty rows. An empty cell counts as 0, so a blank row is measured from latitude 0, longitude 0, in the Atlantic off West Africa. Wrap column formulas in IF(A2:A="", , …) as shown above, and set lookup ranges to exactly your data rows.

The formula is rejected in a European spreadsheet. In locales that use a decimal comma, such as Germany or France, formulas separate arguments with semicolons and the radius is written 3958,8. Either rewrite the formula that way or check the locale under File → Settings.

#NUM! from a shorter ACOS formula. For identical or nearly identical points, rounding can push the cosine a hair above 1, and ACOS of anything above 1 is an error. Wrap the inside in MIN(1, …), or use the haversine formula, which doesn’t have this problem.

Frequently asked questions

Related guides

• Calculate driving distance and travel time between addresses in Google Sheets
• How to geocode addresses in Google Sheets
• How to map addresses from Google Sheets
• All guides

Disclosure: we make MapRows, a Google Sheets add-on mentioned in this guide. We describe the free methods as fairly as we can, including where they are the better choice.