Excel 2007 Find closest match in multiple records 1

[con10t]

I have a spreadsheet that holds information for over 1000 individuals. There are three scores per individual. We have a "model" individual and I want to find the individual that most closely matches the model. It doesn't matter if their scores are higher or lower, just closer.

Model Individual 16, 21, 10

Individual 1, 20, 17, 8
Individual 2, 1, 45, 10
Individual 3, 16, 3, 17

Is there a formula (code?) I can use to find the closest match? (In this case the closest match would be Individual 1 because all three numbers are closer, even though the others have an exact match on one parameter). The formula needs to look at each separate parameter and then determine the closest match overall. It would also be nice to sort them from closest match to least matching. For example,given the above information compared to the model:

Individual 1 is +4, -4, -2;
Individual 2 is -15, +24, 0;
Individual 3 is 0, -18, +7;

Individual 3 is next closest because, overall, he is closer to the model's parameters on each score than Individual 2. Does that make sense?

Thanks for letting me pick your brains! Have a fabu day!

Deborah

 

[SkipVought]

Hi,

Given your example, my ranking would be

1 -2
2 9
3 -11

How are you evaluating? Please state your logic.

By "closest match" do you mean the DIFFERENCE that is closest to ZERO?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dallen43]

Assuming the model is in row 1 and Individual 1 is in row 4, you can sum the absolute differences in cell E4 with the following formula.

=SUM(ABS($B4-$B$1),ABS($C4-$C$1),ABS($D4-$D$1))

Then copy the formula down column E and sort on column E.

The result would be:

Individual 1
Individual 3
Individual 2

This way, being over in one are won't "offset" being under in another.

Deb

 

[con10t]

Skip--Thanks for your reply. I guess, yes, that I mean I want to find the individual who is closest to "zero" on all three parameters, and if there isn't an exact match--zeroes on all parameters--who matches the model the closest.

Dallen/Deb--Thanks for this--I think this is exactly what I was looking for! I'll have to crunch a subset and study up on the ABS function to be certain, but I think this does the trick! I had no idea how to even start; thanks so much for your help!

Deborah