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