Minimum Distance

Welcome to the forum.

How large are the two lists?

All you want to now is the minimum distance, not the nearest point?

You want to calculate great-circle distance?

Post a workbook with some data.

The lists are about 400 locations in total and i'm trying to find the minimum distance to the next coordinate for each. I am using the great-circle distance formula for each of these but there is 399 calculations for each 400 locations, so that's why I feel some vba programming is necessary. Here is an attached worksheet with a sample of 5 locations, using a rudimentary method to find each of the minimum, but with 400 locations, excel runs out of room and this would take quite a while to accomplish.

test min dist.xls

Update: I ended up making 400 macros (yes, it took me 5 hours, but I got it done) and got my data, but I still want to know how to write a function in vba to do this for me because I will definitely be using this kind of method in future research of my own.

Update 2: I started looking further into VBA and I have come up with some code that seems like it should work. Here it is:

Function FindMinDist(Lat1 As Double, Lon1 As Double)
Dim Index As Integer, Min As Double, Dist As Double
Index = 3
Min = 500
While Index < 404
Index = Index + 1
Dist = WorksheetFunction.Acos(Cos(WorksheetFunction.Radians(90 - Lat1)) * Cos(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) + Sin(WorksheetFunction.Radians(90 - Lat1)) * Sin(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) * Cos(WorksheetFunction.Radians(Lon1 - Worksheet.Cells(Index, 3)))) * 3958.756
If Dist < Min And Dist <> 0 Then
Min = Dist
End If
Wend
FindMinDist = Min
End Function

When I try using the function, it gives me back #VALUE! in the cell. I have my latitude in C4:C403 & longitude in D4:D403. Any ideas on how to make this work properly?

Figured it out! All good to go! Wrote it myself. Here it is if anyone is interested:

Function FindMinDist(Lat1 As Double, Lon1 As Double)
    Dim Index As Integer, Min As Double, Dist As Double, Lat2 As Double, Lon2 As Double
    Index = 3
    Min = 500
    While Index < 392
        Index = Index + 1
        Lat2 = Cells(Index, 3).Value
        Lon2 = Cells(Index, 4).Value
        If Cos((90 - Lat1) / 57.2957795130823) * Cos((90 - Lat2) / 57.2957795130823) + Sin((90 - Lat1) / 57.2957795130823) * Sin((90 - Lat2) / 57.2957795130823) * Cos((Lon1 - Lon2) / 57.2957795130823) > 1 Then
        Dist = 0
        Else
        Dist = WorksheetFunction.Acos(Cos((90 - Lat1) / 57.2957795130823) * Cos((90 - Lat2) / 57.2957795130823) + Sin((90 - Lat1) / 57.2957795130823) * Sin((90 - Lat2) / 57.2957795130823) * Cos((Lon1 - Lon2) / 57.2957795130823)) * 3958.756
        End If
        If Dist < Min And Dist <> 0 Then
            Min = Dist
        End If
            Wend
    FindMinDist = Min
End Function

Another way:

Function FindMinDist(lat1 As Double, lon1 As Double, r As Range) As Double
    Const pi As Double = 3.14159265358979
    Const D2R = pi / 180#
    
    Dim iRow As Long
    Dim dMin As Double
    Dim dAng As Double
    
    dMin = 1.79E+308
    
    For iRow = 1 To r.Rows.Count
        dAng = CentralAngle(lat1, lon1, r(iRow, 1).Value2, r(iRow, 2).Value2)
        If dAng < dMin Then dMin = dAng
    Next iRow
    
    FindMinDist = dMin
End Function

Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                      ByVal lat2 As Double, ByVal lon2 As Double) As Double
    ' shg 2008-1111

    ' Returns central angle between two points in RADIANS using Vincenty formula

    Const pi    As Double = 3.14159265358979
    Const D2R   As Double = pi / 180#

    Dim dLon    As Double
    Dim x       As Double
    Dim y       As Double

    ' convert angles from degrees to radians
    lat1 = D2R * lat1
    lat2 = D2R * lat2
    dLon = D2R * (lon2 - lon1) ' delta lon

    x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
    y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
    CentralAngle = WorksheetFunction.Atan2(x, y)
End Function

By passing the range containing the other lat-longs, Excel sees a dependency and will automatically recompute when the data changes. Example usage:

=FindMinDist(D2, E2, D4:E392)

The value returned is in radians, which means you can multiply by the earth radius in your preferred units (meters, miles, nautical miles, ...) to get linear (great circle) distance.

Originally Posted by Mahmut M72

hey shg,
I used the second way and it works perfectly and thank you. How can I find which one is closest? For example, which bus stop?
orijin sheet1 (range c1:d1)

destination sheet2 (range c2: d15000) large data

column c (names) column m (lat) column n(long) column p (min dist)

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread