On 2002-04-10 11:35, mimmo wrote:
anybody know of, or had experience using Excel to perform a Nearest Neighbour Analysis? Basically that involves finding the closest distance between an array of points that have x,y coordinates
thanks
nick
Hi Nick,
I just wrote this UDF which will handle any number of dimensions, but you get to check it!
Requirements are that the data must be a continuous block on the worksheet. The x,y,z,w...coordinates are in columns and each data point is in a row.
Please test this out against some of your data to check if the minimum distance is correct.
When you are satified with the results, I can add a calclulation to find the nearest neighbor data points.
------------------
Function NEAREST_NEIGHBOR(ValRange)
Dim RangeArray As Variant
Dim i As Long, j As Integer, distance As Double, min_dist As Double
If ValRange.Cells.Count = 0 Then
NEAREST_NEIGHBOR = CVErr(xlErrNum)
Exit Function
End If
RangeArray = ValRange.Value
For i = 1 To ValRange.Rows.Count - 1
distance = 0
For j = 1 To ValRange.Columns.Count
distance = distance + (RangeArray(i + 1, j) - RangeArray(i, j)) ^ 2
Next j
If i = 1 Then
min_dist = distance
Else: min_dist = WorksheetFunction.Min(min_dist, distance)
End If
Next i
NEAREST_NEIGHBOR = Sqr(min_dist)
End Function
---------------------
Called as a regular function
=NEAREST_NEIGHBOR(your range)
HTH,
Jay