nearest neighbor

Posted by Joel Cusick on April 24, 2000 10:27 PM

I need to run a "Q test" on a dataset to find and throw out outliers that meet a criteria.

Here is the equation:
Value of number minus its nearest neighbor / range of numbers. ((x-nearest_neighbor)/range of dataset).

Any tricks on a formula for nearest neighbor? After finding each values nearest neighbor, how about a trick to run the calculation?

Posted by Tim Francis-Wright on May 12, 2000 9:40 AM

You can use a quick Visual Basic function to do
this:

Function Nearest(Bit As Double, Stuff As Range)
Application.Volatile
BitRank = Application.WorksheetFunction.Rank(Bit, Stuff)
If BitRank + 1 > Stuff.Count Then
Nearest = Application.WorksheetFunction.Large(Stuff, BitRank - 1)
ElseIf BitRank = 1 Then
Nearest = Application.WorksheetFunction.Large(Stuff, 2)
Else
NearBelow = Application.WorksheetFunction.Large(Stuff, BitRank - 1)
NearAbove = Application.WorksheetFunction.Large(Stuff, BitRank + 1)
If Abs(NearBelow - Bit) &LT; Abs(NearAbove - Bit) Then
Nearest = NearBelow
Else
Nearest = NearAbove
End If
End If
End Function

Say the range is called TestRange, from B1 to B10.
You can call the function as follows:
=nearest(b2,TestRange)
This will give the nearest neighbor to B2
among the other entries. It will only return
the same value as B2 if there are 2 or more
entries with that same value.