Forum Discussion
Excel: Condition formatting problem.
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.
Ok, I will try this, thank you Philip!