Conditional Formatting for lowest ten scores

[Chippy68]

Hi Guys,

I run a few golf pools a year and have a pretty good spreadsheet that automates a lot of stuff (now if only I could tie into alive leaderboard). One of the things people want to see is their lowest 7 scores highlighted. I can total the lowest 7 using the SMALL function, but when using conditional formatting to highlight the lowest 7 it wont stop at 7 if there are ties for the lowest 7, which means it could potentially highlight 8 or 9 scores, which will cause me many unnecessary emails questioning things.

Example
Player Score
A 2
B 2
C 3
D 5
E 2
F 3
G -4
H -6
I -1
J 0
K -2
L -3

The lowest 7 scores from this selection would be -6,-4,-3,-2,-1,0,2......but there are two others scores that are 2, so when I format the lowest 7 to be highlighted, it includes those scores, but they are scores 8 and 9 and therefore they shouldn't be highlighted.

All you golf/excel peeps must be able to figure this one out.

Thanks in advance for any asistance.

Cheers

 

[Chippy68]

The layout is the same for all four rounds we just don't count the bonus player's score in the first two. I'm not worried about adjusting for each of the rounds, I just cant get the formula you gave me to work.

The bonus player is line 18 and Line 17 is Black Filled.

Thanks

 

[jtakw]

A couple more questions,

Are your scores in Column C, Rows 3 thru 16 (18 if including bonus player) as shown in your sample?
Your sample shows 2 duplicated scores -3 and 7 that are within lowest 7 scores in the Column, how do you want to handle that?
My formula will Only highlight 6 lowest if there are 2 duplicates, 5 lowest if there are 3 duplicates, etc.

 

[Chippy68]

Now you can see my problem. I can TOTAL the lowest 7 scores, but if there are duplicate scores that should make up the 7th lowest, then highlighting the lowest 7 won't work, hence me coming to the forum for help.....

And yes my scores are in row 3 thru 16 (18 when including bonus player)

 

[Eric W]

Here's another possibility: Select B3:B18. Click Conditional Formatting > New Rule > Use a formula > and enter:

=AND((RANK($B3,$B$3:$B$18,1)+COUNTIF(B$3:B3,B3)-1)<=7,ISNUMBER(B3))

Choose your fill color.

I can't really take credit for this, I knew there was such a formula and I Googled "Excel rank formula no ties" and adapted it.

 

[Chippy68]

YES!!!!!!!!!!!! Thank you very much. The format painter doesn't work perfectly but I only need to change the column reference in the first section. Thank you very much for your assistance here!