"Find Most Opposite" Function?

[Fealomwen]

Is there a way to search for a row that is "most opposite" to another row?
For instance, if the values in the "initial row" were 2 in column A, 2 in
column B, and -2 in column C, I'd want to find the "most opposite" row where
column A might read -2, column B might read -1, and column C might read 2 (as
opposed to a "similar" row where column A might read 2, column B 1, and
column C -1).

Thanks for any advice

 

[Chip Pearson]

You don't really define what constitutes the what makes one row
different from another and how such a difference is to be quantified.

The method below uses simple Pythagorean geometry to calculate the
distance in N-dimensional space between a reference point (whose
coordinates are the values of the columns in ReferenceRow) and each
point whose coordinates are the values in the other rows. The row that
describes the point farthest away from the reference point is
considered the "most different". The square roots used in the
Pythagorean equation are omitted as they contribute nothing to the
result.

In the function, ReferenceRow is the row number that contains the
coordinates of the point from which the distances of the other points
are to be measured. StartRow is the row number of the first set of
points to be tested. EndRow is the row number of the last row of the
coordinates to be tested. StartCol is the first column containing the
coordinates of each point. EndCol is the ending column containing the
coordinates of each point.

For example,

=MostDifferent(5,6,12,3,8)

This will calculate the distances between the point defined by the
coordinates in row 5 to all the points defined by the coordinates in
rows 6 throught 12, using as coordinates the values in columns 3
through 8. The row number (between 6 and 12) that contains the
coordinates farthest away from the point described by row 5 is
returned.

This is but one of many ways one might define the "most different" row
from another row, but in the absence of more detail, it is perfectly
valid.


Function MostDifferent(ReferenceRow As Long, _
StartRow As Long, _
EndRow As Long, _
StartCol As Long, _
EndCol As Long) As Long

Dim Diff As Double
Dim SaveDiff As Double
Dim R As Long
Dim C As Long
Dim SaveR As Long
For R = StartRow To EndRow
Diff = 0
For C = StartCol To EndCol
Diff = Diff + ((Cells(R, C) - Cells(ReferenceRow, C)) ^ 2)
Next C
If Diff > SaveDiff Then
SaveDiff = Diff
SaveR = R
End If
Next R
MostDifferent = SaveR

End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com