Forum Discussion

Örjan Swedberg

Copper Contributor

Aug 23, 2018

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

Bronze Contributor

Aug 23, 2018

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

Örjan Swedberg
Copper Contributor
Aug 23, 2018
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.
- Philip West
  Iron Contributor
  Aug 23, 2018
  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.
  Orjan.xlsx9 KB
  - Örjan Swedberg
    Copper Contributor
    Aug 24, 2018
    Ok, I will try this, thank you Philip!

Resources