Calculate GPS distance between latitude and longitude coordinates

Convert degrees with RADIANS and clamp ACOS to avoid #NUM!

Trigonometric functions SIN and COS expect radians, not decimal GPS degrees—always wrap Lat and Lon in RADIANS(...). Multiplying the central angle by Earth's mean radius (6,371 km or 3,958.8 miles) gives air/great-circle distance.

Crucial floating-point safeguard: when two rows hold the EXACT same GPS coordinates, SIN(lat)^2 + COS(lat)^2 * 1 can evaluate to 1.0000000000000002 in IEEE-754 arithmetic, causing ACOS(>1) to throw #NUM!! Clamping with MEDIAN(-1, 1, ...) prevents ACOS arguments outside [-1, 1]; it does not remove all floating-point error.

Spherical distance formula in km and miles

Between central London and central Paris, great-circle distance is 343.56 km (213.48 miles).

=LET(latRadA, RADIANS(A2), latRadB, RADIANS(C2), dL, RADIANS(D2 - B2), ROUND(6371 * ACOS(MEDIAN(-1, 1, SIN(latRadA)*SIN(latRadB) + COS(latRadA)*COS(latRadB)*COS(dL))), 2))

Statute miles variant (R = 3958.8 mi)

Replace Earth radius 6371 km with 3958.8 miles (or multiply km by 0.621371).

=LET(latRadA, RADIANS(A2), latRadB, RADIANS(C2), dL, RADIANS(D2 - B2), ROUND(3958.8 * ACOS(MEDIAN(-1, 1, SIN(latRadA)*SIN(latRadB) + COS(latRadA)*COS(latRadB)*COS(dL))), 2))

Inputs and interpretation

The displayed ACOS expression is the spherical law of cosines, not the haversine expression. It approximates Earth as a sphere; it is not road distance, terrain distance, or an ellipsoidal surveying calculation. Use latitude in [-90, 90] and longitude in [-180, 180] in decimal degrees. Very short distances are sensitive to roundoff; a haversine or robust geodesic implementation is preferable there.

Further reading

• Microsoft Support — ACOS function
• Movable Type Scripts — spherical distance formulas