Haversine formula in Excel: distance between two coordinates

The haversine formula

a = sin²(Δlat ÷ 2) + cos(lat₁) × cos(lat₂) × sin²(Δlon ÷ 2)

distance = 2 × R × asin(√a)

Δlat and Δlon are the differences in latitude and longitude, lat₁ and lat₂ the two latitudes, and R the Earth's mean radius. The formula treats the Earth as a sphere, which is accurate to within about 0.5% compared with the true, slightly flattened shape. That is far better than any straight-line shortcut and plenty for store networks, delivery zones, sales territories or travel estimates.

Step 1: coordinates and radians

Store coordinates in decimal degrees: latitude from −90 to 90 (south is negative), longitude from −180 to 180 (west is negative). New York is 40.7128, −74.0060. If your data is in degrees, minutes and seconds, convert first: degrees + minutes/60 + seconds/3600.

Excel's SIN, COS and ASIN work in radians, not degrees, so every angle goes through RADIANS() first. For New York to Chicago, Δlat = RADIANS(41.8781 − 40.7128) = 0.0203 and Δlon = RADIANS(−87.6298 − (−74.0060)) = −0.2378. The sign does not matter, because the formula squares the sines.

Step 2: from a to the distance

Put the pieces together: a = SIN(Δlat/2)^2 + COS(RADIANS(lat₁)) × COS(RADIANS(lat₂)) × SIN(Δlon/2)^2 = 0.0080. The central angle is c = 2 × ASIN(SQRT(a)) = 0.1796 radians, and the distance is R × c = 3,958.8 × 0.1796 = 711.0 miles. For kilometres, use R = 6,371 or multiply the miles by 6,371 ÷ 3,958.8.

In one cell, with $ anchors on the origin and the radius, the formula copies down a whole list. The example gives Boston 190 miles, Washington, DC 204, Toronto 342, Atlanta 746, Miami 1,092 and Los Angeles 2,446 from New York.

Worked example: distances from New York in miles and km

Coordinates (approximate city centres) and the Earth's radius are yellow inputs. Column D is the one-cell haversine formula, copied down. The lower block repeats New York to Chicago step by step and compares it with two other formulas.

Worked example: distances from New York in miles and km

	A	B	C	D	E
1	Place	Latitude	Longitude	Distance (mi)	Distance (km)
2	New York (origin)	40.7128	-74.0060
3	Boston	42.3601	-71.0589	190	306
4	Washington, DC	38.9072	-77.0369	204	328
5	Chicago	41.8781	-87.6298	711	1,144
6	Atlanta	33.7490	-84.3880	746	1,200
7	Toronto	43.6532	-79.3832	342	550
8	Miami	25.7617	-80.1918	1,092	1,758
9	Los Angeles	34.0522	-118.2437	2,446	3,936
10
11	Constants	Value
12	Earth radius (miles)	3,958.8
13	Earth radius (km)	6,371
14
15	New York → Chicago, step by step	Value
16	Δlat in radians	0.0203
17	Δlon in radians	-0.2378
18	a = sin²(Δlat/2) + cos·cos·sin²(Δlon/2)	0.0080
19	c = 2 × ASIN(√a), in radians	0.1796
20	Distance = R × c (mi)	711.0
21	Same with the law of cosines (mi)	711.0
22	Wrong: flat degrees × 69 (mi)	943.5

Great-circle distances on a spherical Earth, accurate to about 0.5%. Road distances are longer.

The spherical law of cosines alternative

Many Excel answers online use a shorter formula, the spherical law of cosines:

=ACOS(SIN(RADIANS(lat1))*SIN(RADIANS(lat2))+COS(RADIANS(lat1))*COS(RADIANS(lat2))*COS(RADIANS(lon2-lon1)))*R

For New York to Chicago it gives the same 711.0 miles. The difference shows up at very short distances: when two points are only metres apart, the value inside ACOS is so close to 1 that rounding errors dominate. Haversine stays accurate at every distance, so it is the safer default.

Straight-line distance versus road distance

Never subtract coordinates and use Pythagoras on the degrees. One degree of latitude is about 69 miles everywhere, but one degree of longitude shrinks towards the poles, to about 52 miles at New York's latitude. The flat shortcut puts Chicago 943 miles from New York, a third too far.

The haversine result is the distance as the crow flies. Roads are longer: a common approach multiplies straight-line distance by a circuity factor of roughly 1.2 to 1.4, depending on the road network, and then applies speeds to estimate drive time. For the cost side of those miles, see the cost per mile guide.

Common mistakes

• Forgetting RADIANS. SIN and COS in Excel expect radians. Passing degrees gives a wrong distance without any error message.
• Using Pythagoras on degrees. Longitude degrees shrink towards the poles, so the flat shortcut overstates east-west distances.
• Mixing up the signs. West longitudes and south latitudes are negative. A missing minus sign puts a point on the other side of the world.
• Swapping latitude and longitude. Latitude comes first in most data sources, but not in all. Check one known distance.
• Copying ATAN2 versions from other languages. Excel's ATAN2 takes (x, y), the reverse of most programming languages. The ASIN version avoids the problem.
• Mixing units. R = 3,958.8 gives miles, R = 6,371 gives kilometres. Keep the radius in one labelled cell.