Forum Discussion
Excel: Condition formatting problem.
I can’t format only the 10 highest values in cell’s if for ex. column J1 and K1 in my example have the same values, haven’t found any function/formel to sort out/rank same values by using the columns (in this case low to high) as a preference.
Ex. The condition formatting rule is used on row 1.
4 Replies
Mr. Swedberg
I don't know if I get you right, but if you want to sort it by row - pls see below image - DATA>Sort>Option
HTH
Hi Lorenzo!
Not exactly, please see my new example below, I want format the 10 highest values green and if there is 2 with the same value positioned as number 10 and 11 (more columns will be used) the one with the lowest column will formatted green.
Ex. row 1 is with the format rule, row 3 is how I want it to be.
Can you use a helper row?
I think if you have something like this:
=A3+(0.000000001 - (COLUMN(A3)*0.00000000001)) for each of your numbers you will end up with (in your example)
A4: 199.0000000009900
b4: 199.0000000009800
C4: 200.0000000009700
D4: 200.0000000009600
etc..
Then your condition for formatting row 3 would be something like:
=IF(A4>LARGE($A$4:$K$4,11),TRUE,FALSE)
the numbers behind the decimal should be small enough not to interfere with your ranking, but will help you take the left most instance for any given numbers..
You can probably hide the 'helpers' away on another workbook or in some hidden columns on the same row..
have attached an example.
