Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Similar Data in worksheets | Excel Discussion (Misc queries) | |||
Finding similar data in a spreadsheet | Excel Discussion (Misc queries) | |||
Finding the MAX number while considering similar names | Excel Discussion (Misc queries) | |||
Finding and Highlighting Similar but not Identical Cells in a Colu | Excel Discussion (Misc queries) | |||
Finding similar data or numbers in two columns | New Users to Excel |