VBA code for anyone interested. Calculates distance between two points using different assumptions about the shape of the earth
See also this thread where the code was used to plot flight times against predicted distance.
---
'enter latitude and longitude for the two points, in degrees (not radians)
'Calculates the distance between the two points on the assumption that the earth is spherical
Function haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double)
Dim x As Double, r As Double
r = 6371#
'convert to radians
lat1 = lat1 * WorksheetFunction.Pi() / 180
long1 = long1 * WorksheetFunction.Pi() / 180
lat2 = lat2 * WorksheetFunction.Pi() / 180
long2 = long2 * WorksheetFunction.Pi() / 180
haversine = 2 * r * WorksheetFunction.Asin(Sqr((1 - Cos(lat2 - lat1)) / 2# + Cos(lat1) * Cos(lat2) * (1 - Cos(long2 - long1)) / 2#))
End Function
'enter latitude and longitude for the two points, in degrees (not radians)
'Calculates the distance between the two points on the AE map
Function FEdistance(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double)
Dim l1 As Double, l2 As Double, l3 As Double, a1 As Double, l4 As Double, h1 As Double
l1 = (90 - WorksheetFunction.Max(lat1, lat2)) * 111.19
a1 = (long2 - long1) * WorksheetFunction.Pi() / 180
h1 = Sin(a1) * l1
l2 = Cos(a1) * l1
l3 = (90 - WorksheetFunction.Min(lat1, lat2)) * 111.19
l4 = l3 - l2
FEdistance = Sqr(l4 ^ 2 + h1 ^ 2)
End Function