Finding Similar Data
 

Hello,

I would like to set conditional formatting to highlight the data value from
five different columns that is most similar to another data value in a sixth
column (there will be one data point between all of the five columns that is
closest to the sixth column). I would then like to sort the data based on
the similarities. I don't know if there is a way for conditional formatting
to pick out the closest value---if this is possible, please let me know!

Thanks for any help you can provide.

Finding Similar Data
 

Are you want ting sort or use conditional formatting?

In A1:E1 I have these values
4 11 12 7 8
In G1 I have the test number (I used 6)
I selected A1:E1 and used this in the Formula Is box of the conditional
formatting dialog
=A1-$G$1=MIN(ABS($A$1:$E$1-$G$1))
The cell with the value 7 got highlighted as hoped for
If I change the 12 to 5 then bot the 5 and the 7 are highlighted

This is interesting since to do the same test in cell A2:E2, with
=ABS(A1-$G$1)=MIN(ABS($A$1:$E$1-$G$1))
I must enter it as an array formula but I did not need to use
CTRL+SHIFT+ENTER with the Conditional Format dialog

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"jebben" wrote in message
...
Hello,

I would like to set conditional formatting to highlight the data value
from
five different columns that is most similar to another data value in a
sixth
column (there will be one data point between all of the five columns that
is
closest to the sixth column). I would then like to sort the data based on
the similarities. I don't know if there is a way for conditional
formatting
to pick out the closest value---if this is possible, please let me know!

Thanks for any help you can provide.