5

I am trying to implement Haversine formula into excel function. Its looks like this:

Public Function Haversine(Lat1 As Variant, Lon1 As Variant, Lat2 As Variant, Lon2 As Variant)
Dim R As Integer, dlon As Variant, dlat As Variant, Rad1 As Variant
Dim a As Variant, c As Variant, d As Variant, Rad2 As Variant

R = 6371
dlon = Excel.WorksheetFunction.Radians(Lon2 - Lon1)
dlat = Excel.WorksheetFunction.Radians(Lat2 - Lat1)
Rad1 = Excel.WorksheetFunction.Radians(Lat1)
Rad2 = Excel.WorksheetFunction.Radians(Lat2)
a = Sin(dlat / 2) * Sin(dlat / 2) + Cos(Rad1) * Cos(Rad2) * Sin(dlon / 2) * Sin(dlon / 2)
c = 2 * Excel.WorksheetFunction.Atan2(Sqr(a), Sqr(1 - a))
d = R * c
Haversine = d
End Function

But when im testing it I am getting wrong distance... I dont understand why. For coordinates used in this topic : Function to calculate distance between two coordinates shows wrong I am getting 20013,44 as output. Anyone knows what is wrong here? Cant find my mistake...

Community
  • 1
  • 1
banshe
  • 75
  • 2
  • 9
  • Just a question: why did you choose "Variant" as a data type, and not "double"? Second, did you already try to make a simple Excel file for this, using Excel formulas? That would make it easier to understand what might be going wrong. – Dominique Feb 03 '16 at 12:52

2 Answers2

9

Atan2 is defined back to front in Excel compared to JavaScript i.e. Atan2(x,y) rather than Atan2(y,x).

You need to reverse the order of the two arguments:-

c = 2 * Excel.WorksheetFunction.Atan2(Sqr(1 - a), Sqr(a))

See this

So

=haversine(59.3293371,13.4877472,59.3225525,13.4619422)

gives

1.65 km

which is the correct distance as the crow flies.

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

Great tool! Just underscoring that the result will be in kilometers, so if you want miles multiply the result by 0.62137.

John Joseph
  • 1,003
  • 1
  • 10
  • 20