Forum Discussion

null null's avatar
null null
Copper Contributor
Sep 01, 2018

conditional formatting

I'm trying to highlight the lowest 10 scores out of 20 for my golf handicap. I used conditional formatting rule "Format only top or bottom values" and I used Bottom with the value 10 as the ranking v...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 03, 2018

Using the same logic as in previous post it could be done without helper columns applying the formula to the rule like

=D4*(1+COLUMN(D4)/10000000000)<=AGGREGATE(15,6,$D4:$W4*(1+COLUMN($D4:$W4)/10000000000),10)

I'm not sure what shall be the logic if blank cells, thus ignored that case.

 

Second sheet in attached file.

  • null null's avatar
    null null
    Copper Contributor
    Sep 04, 2018

    Thank all that replied, I received a method that I modified and it works here is the formula I used:

    =Sheet1!I4+(COUNTIF(Sheet1!$D$4:Sheet1!I4,Sheet1!I4)-1)*0.1

    I added "sheet1" to the work book and copied the values used in my master, the formula adds a fraction to like values and eliminates the highest fraction value thus leaving the lowest 10 values to accept the formatting.

     

    Thank all of you that replied.

  • Philip West's avatar
    Philip West
    Iron Contributor
    Sep 04, 2018

    I think he deleted his account? Null Null?

    Anyway, I've learnt someone new from that formula so thanks for that. That's very clever.

Resources