Finding Similar Data

  • Thread starter Thread starter jebben
  • Start date Start date
J

jebben

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.
 
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
 
Back
Top