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

 

[Gerald Higgins]

This is probably do-able but how exactly do you decide which of the 3 scores of exactly 2 are to be included in the lowest 7 ?

 

[Chippy68]

It doesn't matter as long as it only highlights one of three of the "2's" in this scenario.

 

[jtakw]

It doesn't matter as long as it only highlights one of three of the "2's" in this scenario.

Hi,

Here're 2 ways, D2 formula will highlight First duplicated rank, E2 formula will highlight Last duplicated rank.

Use either for your CF Formula to highlight Column/Cells:

Book1
	A	B	C	D	E
1	Player	Score	Score	B Column	C Column
2	A	2	2	TRUE	FALSE
3	B	2	2	FALSE	FALSE
4	C	3	3	FALSE	FALSE
5	D	5	5	FALSE	FALSE
6	E	2	2	FALSE	TRUE
7	F	3	3	FALSE	FALSE
8	G	-4	-4	TRUE	TRUE
9	H	-6	-6	TRUE	TRUE
10	I	-1	-1	TRUE	TRUE
11	J	0	0	TRUE	TRUE
12	K	-2	-2	TRUE	TRUE
13	L	-3	-3	TRUE	TRUE
Sheet44

Cell Formulas
Range	Formula
D2	=AND(RANK(B2,B$2:B$13,1)<=7,COUNTIF(B$2:B2,B2)=1)
E2	=AND(RANK(C2,C$2:C$13,1)<=7,COUNTIF(C$2:C2,C2)=COUNTIF(C$2:C$13,C2))

 

[Chippy68]

Thank You for this. Is the above example suggesting I need to add in a column D or E to run a true/false statement in order for the formula to work?

Line 2 in my spreadsheet is a black line which is where I plugged in the formula and made it relate to my cells and it didn't work. New to posting in here so I would be happy to add in a section of the worksheet to show you but I can't figure out how to.

Thanks

 

[jtakw]

No, You do Not need to add any Columns at all, I showed the Columns in my sample just to demonstrate that the logic is correct.

That's why I mentioned "Use either for your CF Formula to highlight Column/Cells"

Using my sample in Post #4 for reference (as I don't know how your data is laid out):

Assuming bottom 7 scores I want to highlight are within B2:B13,
Click B2, drag down to B13 (in other words, select B2:B13),
Click Conditional Formatting, click New Rule,
Select "Use a formula to..........."
Enter one of the formula of your choice from my post, or copy and paste,
Select Format, choose Fill, and Fill color, OK, OK

Keep in mind, you need to change the cell references in my formulas according to your data, if you need help with that, please let me know what Column and Range your data is in.

 

[Chippy68]

Your help is amazing, but for some reason your formula isn't working, it might be me....

Can I email you the spreadsheet to have a look at with references to show you where I am looking to achieve this? If so, what is the easiest way to send it.

Thanks

 

[jtakw]

Can you show a screenshot and/or describe how and where your data is (with Column and Row references).

Otherwise, try uploading to a free file host like Dropbox.

 

[Chippy68]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rory McIlroy[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Jordan Spieth[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Alex Noren[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Danny Willett[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Jimmy Walker[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Louis Oosthuizen[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Charl Schwartzel[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Emiliano Grillo[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Bill Haas[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Kevin Chappell[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Ross Fisher[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Zach Johnson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Billy Hurley III[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Curtis Luck[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]This Row has Black Fill[/TD]
[TD]In[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD]Soren Kjeldsen[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Sorry this looks so sloppy, would love to know how you get a nice spreadsheet in here like yours, I was unable to post a screen shot in here...

For the first and second rounds we don't include the bonus player (each round has its own worksheet), for the third and fourth the bonus player is available to count as one of your 7 best.

Let me know if you need more detail in this.

 

[jtakw]

A couple questions:

In your sample above, who's the "bonus player" that's Not to be counted until 3rd and 4th rounds? What round is this sample?
Since each round has it's own worksheet, then the CF formula would just need cell range references changed, should be a simple fix.

EDIT: look here for how to add attachments, I use the HTML Maker: https://www.mrexcel.com/forum/about-board/508133-attachments.html