Conditional Formatting - Sports (Golf) Leaderboard

[gorodn444]

Hello, first time poster, so please be gentle.

I help run golf competitons for junior golfers and use Excel for all my leaderboards etc. We run a season long competiton where the best 7 scores from all 14 tournaments count towards your end of season score.

I'd like to find a way of highighting each player's best/lowest 7 scores that have been used. I've tried using the Bottom 7 in conditional formatting. Unfortunately, if their 7th lowest score is equal to the 8th/9th etc it highights them all. In certain circumstances plyers can have up to 9 or 10 scores highlighted when I only need a maximum of 7 highlighted.

Is this possible. to do?

 

[Kaper]

As it is seen on the screenshot the results are always whole (integer) numbers.
You may use a cell content+0.000001*column_number as a value.
And select 7 highest (or shall they be lowest in golf?) numbers.
There will be only 7 such numbers as every 5 in case of first palyer in your screenshot is different: F1=5.000006, G1=5.0000007 etc.

 

[Peter_SSs]

Welcome to the MrExcel board!

What about something like this?

24 08 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2				1	1	3			7	4				7	3	1
3				7	8	7	0	1	3	7	1	6	7	6	3	6
4				0	1	5	4	3	7	7	0	4	7	4	0	0
5						4							5		1
6				3	3	3	3	2	2	3	3	3	3	3	3	3
Lowest 7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:Z6	Expression	=AND(D2<>"",COUNTIF($D2:$Z2,"<"&D2)+COUNTIF($D2:D2,D2)<=7)	text	NO

 

[felixstraube]

What does the E stand for?

 

[felixstraube]

I suppose that it means Even that is we can take it as 0 points. Is that correct?

If that is so then how about this? Taking the idea from @Kaper .
We create a named range:
With the cell F2 selected, create the named range named "HighLight" with this formula:

=LET(n, Sheet2!$F2:$S2, d, IF(n="", "", IF(n="E", 0, n)), v, TAKE(SORT(FILTER(d+SEQUENCE(,14)/100, d<>""),,,1),,7 ), nv, IF(Sheet2!F2="E", 0, Sheet2!F2), ISNUMBER(MATCH(nv+(COLUMN(Sheet2!F2)-COLUMN(Sheet2!$E$1))/100, v, 0)) )

And then we use this named range in the CF:

here is the working file to download: GoldHighLight.zip

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Id	Player number		Rounds played	Total score
2	1	Player 1				5	5	5	5	1			6	6	5	4	7	5
3	2	Player 2				8	8	7		3	8		10	8	8	14	8	7	1
4	3	Player 3				11	E	11	8		8	11	14	11	12	8	11	5	7
5
6
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:S11	Expression	=HighLight	text	NO

 

[Peter_SSs]

I suppose that it means Even that is we can take it as 0 points.

I had forgotten about the "E" (= "Even" = 0)

This would be my suggestion
Format the score entry columns with a Custom number format of +0;-0;"E"

So for "even" scores just enter a 0. For example, cell G3 below contains a 0, not an "E"

Then use the same Conditional Formatting rule I suggested above.

24 08 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2				+1	+1	+3			+7	+4				+7	+3	+1
3				+7	+8	+7	E	+1	+3	+7	+1	+6	+7	+6	+3	+6
4				E	+1	+5	+4	+3	+7	+7	E	+4	+7	+4	E	E
5						+4							+5		+1
6				+3	+3	+3	+3	+2	+2	+3	+3	+3	+3	+3	+3	+3
7				-1	-1	-1	-1	-3	-1	-1	-2	-1	-1	E	E	E
8				+1		-5	E		+3	-3	+1	+1	E	-4	-2
9
10
Lowest 7 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:Z10	Expression	=AND(D2<>"",COUNTIF($D2:$Z2,"<"&D2)+COUNTIF($D2:D2,D2)<=7)	text	NO

 

[gorodn444]

As it is seen on the screenshot the results are always whole (integer) numbers.
You may use a cell content+0.000001*column_number as a value.
And select 7 highest (or shall they be lowest in golf?) numbers.
There will be only 7 such numbers as every 5 in case of first palyer in your screenshot is different: F1=5.000006, G1=5.0000007 etc.

Yes, they are all whole numbers.

 

[gorodn444]

Love this idea. Thank you very much,

 

[gorodn444]

I had forgotten about the "E" (= "Even" = 0)

This would be my suggestion
Format the score entry columns with a Custom number format of +0;-0;"E"
View attachment 116040

So for "even" scores just enter a 0. For example, cell G3 below contains a 0, not an "E"

Then use the same Conditional Formatting rule I suggested above.

24 08 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2				+1	+1	+3			+7	+4				+7	+3	+1
3				+7	+8	+7	E	+1	+3	+7	+1	+6	+7	+6	+3	+6
4				E	+1	+5	+4	+3	+7	+7	E	+4	+7	+4	E	E
5						+4							+5		+1
6				+3	+3	+3	+3	+2	+2	+3	+3	+3	+3	+3	+3	+3
7				-1	-1	-1	-1	-3	-1	-1	-2	-1	-1	E	E	E
8				+1		-5	E		+3	-3	+1	+1	E	-4	-2
9
10
Lowest 7 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:Z10	Expression	=AND(D2<>"",COUNTIF($D2:$Z2,"<"&D2)+COUNTIF($D2:D2,D2)<=7)	text	NO

This is brilliant. Thank you.

 

[Peter_SSs]

This is brilliant. Thank you.

You're welcome. Glad to contribute. Thanks for the follow-up.