Based on the reasonably accurate assumption that Earth is a smooth sphere, the distance calculation is based on spherical trigonometry. However, there can be numerical problems with calculations if the two points are close together or near opposite poles. Vincenty's formula is used to provide accurate calculations under all conditions:
Wikipedia reference
Vincenty, Thaddeus, 1975. Direct and Inverse Solutions of Geodesics on the Ellipsoid with Application of Nested Equations. Survey Review 23 (176): 88–93.
Kingston Road, Tolworth, Surrey: Directorate of Overseas Surveys.
D = R•arctan(y/x), where
x = sin(λ1)•sin(λ2) + cos(λ1)•cos(λ2)•cos(ΔL)
y = sqrt[(cos(λ2)•sin(ΔL))2 + (cos(λ1)•sin(λ2) -
sin(λ1)•cos(λ2)•cos(ΔL))2]
and
R (Earth's mean equatorial radius) = 6378 km
Be sure to use appropriate units when doing these calculations in Excel or with many programming languages which expect angles to be expressed in radians rather than degrees. Also, in Excel and many programming languages, there is a separate arctan function, often denoted by atan2() rather than atan(), which requires as input both x and y (as defined above), so that cases where the denominator equals 0 can be handled properly. In some languages (such as JavaScript, used in this application), the syntax is atan2(y,x) but in Excel, the formula is ATAN2(X,Y). Regardless of the syntax, it is the value of x which must be tested for a value of 0.
The answer is rounded to the nearest 0.01 km (10 m).
My thanks to James Clegg (location and affiliation unknown) who has pointed out a major problem with earlier versions of this calculation.