Search code examples
google-sheetsgoogle-sheets-formulags-conditional-formatting

Google sheets conditional formatting of lowest n

I keep sheets with scores for players, and the 10 lowest scores are counting. What I would like to do is to color the 10 first lowest scores in light green. The last highest number of those 10 in darker green. (It's the score to beat in order to improve your total score) And the lowest score in yellow.

I guess it is easier to explain with an example: https://docs.google.com/spreadsheets/d/1BSGfpzmaibsR4dxHqFgmYmJq7RtyAEV8uRCB0S3Fa4A/edit?usp=sharing

Feel free to copy your own set and try out:) enter image description here

Solution

  • The solution was to get Rank without ties, and this would give you Rank without ties:

    =RANK(B2,$B2:2,1)+COUNTIF($B2:B2,B2)-1

    With correct rank it's easy to add custom formatting for the highest of the 10 lowest:

    =RANK(B2,$B2:2,1)+COUNTIF($B2:B2,B2)-1=10

    Since it works on a range, B2 in the formula will be substituted for each cell in the range.

    So the problematic cell O4 will give:

    RANK(O2,$B2:2,1) equals 10
COUNTIF($B2:O2,O2) equals 2
so
RANK(B2,$B2:2,1)+COUNTIF($B2:B2,B2)-1 equals 11 ie it will not be colored

    Had to add an extra condition to color the highest scores for players with less than 10 scores.