Conditional formatting for specific number of cells

[dcobe]

Hello excellent Excel-people,

I need your support in getting a conditional formatting correct.
The excel is for a sporting event, showcasing the results of each race. Due to special rules, not all races are counted towards your total score. For every three races that happen, 1 score (the worst one) gets removed. While the calculation of the total points is not a problem, I'm struggling to apply the conditional formatting so that it is directly obvious which results have been removed.


The sheet is setup the following way:

Columns A-F, general meta data about participants.
Columns G - BD, race results (up to 50 races)
Column BF, total score (after adjustment)
Cell E4 contains the number of scores that need to be removed

The data is from row 6 to row 200.

Problem:
Let's say there were 13 races, so the highest 4 scores need to be removed.
In the sheet I want to show all 13 results and use conditional formatting to strikethrough the 4 scores that need to be removed.
However, all formulas I've tried so far only work if the highest scores aren't duplicates. For example, if the race the participant has 5 times a score of 40 it will strike through all 5 even though it should only format 4 of them.

I need a formula that recognizes how many cells it needs to adjust and only applies the conditional formatting to those accordingly.

Thanks a lot in advance for your help!

Best

dcobe

 

[Vogateer]

Can you paste your data into a table or else share the file on a file-sharing site so that we don't need to retype all that data from the image? There is an add-in that can help you generate the code, and it's found here:
XL2BB

 

[dcobe]

I have uploaded the sample here: Excel

Thanks a lot!

 

[Kaper]

May be someone else can show shorter formula. But this seems to work:

Select all range G6:BD65 and add a formatting rule based on formula. As a formula copy:

=AND((COUNTIF(G6:$BD6,SMALL($G6:$BD6,INT(COUNT($G6:$BD6)/3)))+COUNTIF($G6:$BD6,"<"&SMALL($G6:$BD6,INT(COUNT($G6:$BD6)/3))))<=INT(COUNT($G6:$BD6)/3),G6<=SMALL($G6:$BD6,INT(COUNT($G6:$BD6)/3)))

Note both ends of range mixed addressing $G6:$BD6 and in one case one end relative the other end mixed: G6:$BD6
And ... that's it (see screenshot)

 

[Vogateer]

I might have misunderstood the problem, and my formula is far longer, but I had to create an additional sheet to get it to work like I thought it should. I'll post it in case it helps someone with a similar problem where Kaper's doesn't work.

I duplicated your whole first sheet and named that sheet "ConditionalFormat". I then deleted all the scores and used the following (embarassing?) formula to give a TRUE/FALSE result for whether each score would be excluded as one of the four highest scores. (TRUE would mean strikethrough formatting for one of the 4 largest results).

That formula is in the ConditionalFormat sheet, from G6 to G65 (or how ever many rows you have), and you can put this in G6 and drag it down:

=LET(scores,Overall!G6:BD6,
col, COLUMN(scores),
first_col, CHOOSECOLS(COLUMN(OFFSET(scores,0,0,1)),1),
 seq, TRANSPOSE(SEQUENCE(COLUMNS(scores))),
seq_list,scores + seq/1000,
seq_list_sorted, SORT(seq_list,1,-1,TRUE),
seq_list_top_4, LARGE(seq_list_sorted,{1,2,3,4}),
col_list_top_4, ROUND(MOD(seq_list_top_4,1)*1000,0),
T_F_column_list, IF(ISNUMBER(MATCH(COLUMN(scores)-first_col+1,col_list_top_4,0)), TRUE, FALSE),
T_F_column_list)

Adjust the Overall!G6:BD6 range accordingly if your first row of scores is different. This range should only include the results columns.

Then the conditional formatting is pretty simple:
In Overall sheet, conditional formatting range: =$G$6:$BD$66
The conditional formatting formula is:

=ConditionalFormat!G6

The only way this works is if you can keep the ConditionalFormat sheet where each cell is right in line with the cell in the Overall sheet, so you might have to be careful about keeping those sheet's structures in sync.

I'm not great at this stuff, but that was my verbose solution for the problem as I understood it. Hope it might help someone, or maybe others who know their stuff can talk about why this is an example of what not to do .

 

[Kaper]

If we consider usage in recent Excel versions and especially usage of LET function the formatting formula could be (as I understant this approach is similar to proposed by @Vogateer) much shorter:

=LET(r,$G6:$BD6,x,IF(r="","",r-COLUMN(r)/100),y,INT(COUNT(x)/3),G6-COLUMN(G6)/100<=SMALL(x,y))

of with my approach from post #4:

=LET(r,$G6:$BD6,c,INT(COUNT(r)/3),q,SMALL(r,c),AND((COUNTIF(G6:$BD6,q)+COUNTIF(r,"<"&q))<=c,G6<=q))

By the way - I (in both above formulas) proposed marking rightmost equal results as not counted.
If you think of opposite direction, so in case of 3 equal results 2 leftmost shall be marked as not counting, small changes shall be made to formulas.
Adding shall be used instead of deduction in first formula above (twice)

=LET(r,$G6:$BD6,x,IF(r="","",r+COLUMN(r)/100),y,INT(COUNT(x)/3),G6+COLUMN(G6)/100<=SMALL(x,y))

and in the second formula (this is tone presented on the screenshot) relative-mixed addressing shall be replaced with mixed-relative so:

=LET(r,$G6:$BD6,c,INT(COUNT(r)/3),q,SMALL(r,c),AND((COUNTIF($G6:G6,q)+COUNTIF(r,"<"&q))<=c,G6<=q))

 

[Vogateer]

If we consider usage in recent Excel versions and especially usage of LET function the formatting formula could be (as I understant this approach is similar to proposed by @Vogateer) much shorter:

=LET(r,$G6:$BD6,x,IF(r="","",r-COLUMN(r)/100),y,INT(COUNT(x)/3),G6-COLUMN(G6)/100<=SMALL(x,y))

of with my approach from post #4:

=LET(r,$G6:$BD6,c,INT(COUNT(r)/3),q,SMALL(r,c),AND((COUNTIF(G6:$BD6,q)+COUNTIF(r,"<"&q))<=c,G6<=q))

By the way - I (in both above formulas) proposed marking rightmost equal results as not counted.
If you think of opposite direction, so in case of 3 equal results 2 leftmost shall be marked as not counting, small changes shall be made to formulas.
Adding shall be used instead of deduction in first formula above (twice)

=LET(r,$G6:$BD6,x,IF(r="","",r+COLUMN(r)/100),y,INT(COUNT(x)/3),G6+COLUMN(G6)/100<=SMALL(x,y))

and in the second formula (this is tone presented on the screenshot) relative-mixed addressing shall be replaced with mixed-relative so:

=LET(r,$G6:$BD6,c,INT(COUNT(r)/3),q,SMALL(r,c),AND((COUNTIF($G6:G6,q)+COUNTIF(r,"<"&q))<=c,G6<=q))[/CODE

[/QUOTE]

I appreciate you posting this and giving me some more patterns to keep in mind. Much shorter and easier to read than mine.

 

[Fluff]

Another option

formatting-sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3		Overall Ranking
4				Streicher	2
5		Lycra	Rank	Vorname	Nachname		Race 1	Race 2	Race 3	Race 4	Race 5	Race 6	Race 7	8
6		5	1	Andrew	Mays		3	3	40	3	2	2	2
7		49	2	Brandon	Diaz		33	33	13	7	4	4	4
8		7	3	Michael	Evans		4	4	14	38	17	9	9
9		2	4	Brandi	Harrington		2	2	19	6	26	43	43
10		1	5	Steven	Hill		1	1	23	56	22	41	41
11		14	6	Jennifer	Morse		11	11	44	25	11	6	6
12		92	7	Benjamin	Miller		57	57	43	4	3	3	3
13		13	8	Jose	Barnes		10	10	4	28	38	20	20
14		18	9	Kimberly	Shepard		13	13	52	32	15	8	8
15		48	10	Edward	Rojas		32	32	49	15	6	5	5
16		72	11	Diamond	Carter		46	46	7	13	5	32	32
17		84	12	Alexander	Mcgrath		53	53	41	1	1	1	1
18		17	13	Tammy	Lewis		12	12	35	58	23	13	13
19		10	14	Tina	Alvarado		7	7	2	59	59	30	30
20		76	15	Casey	Adams		48	48	1	9	28	16	16
21		26	16	Christine	Chapman		21	21	9	10	29	17	17
22		9	17	Sandra	Castillo		6	6	8	52	55	29	29
23		44	18	Jerry	Benitez		30	30	6	26	12	37	37
24		42	19	Rebecca	Hodges		28	28	24	47	20	11	11
25		25	20	Elizabeth	Gates		20	20	17	16	33	18	18
26		23	21	Melissa	Jimenez		18	18	27	37	16	39	39
27		82	22	Emma	Becker		52	52	5	19	7	33	33
28		12	23	Mariah	Johnson		9	9	29	34	42	22	22
29		19	24	Kim	Wilson		14	14	59	31	14	38	38
30		95	25	Jesus	Lucero		58	58	37	27	13	7	7
31		55	26	Jacqueline	Fuller		36	36	16	20	8	34	34
32		24	27	James	Martin		19	19	38	22	36	19	19
33		35	28	Noah	Little		26	26	36	44	19	10	10
34		65	29	Amber	Reynolds		43	43	47	2	24	14	14
35		22	30	Samantha	Figueroa		17	17	15	51	54	28	28
36		53	31	Brenda	Savage		35	35	51	8	27	15	15
37		8	32	Rhonda	Perry		5	5	46	48	51	27	27
38		28	33	Melissa	Chung		23	23	10	36	44	24	24
39		21	34	Jason	Johnson		16	16	30	23	37	49	49
40		73	35	Jason	Joseph		47	47	31	24	10	36	36
41		40	36	Luis	Anderson		27	27	3	33	41	51	51
42		97	37	James	Kim		59	59	33	21	9	35	35
43		20	38	Rachel	Smith		15	15	53	46	50	26	26
44		30	39	Christopher	West		24	24	57	11	30	44	44
45		43	40	Kenneth	Jackson		29	29	25	30	40	21	21
46		90	41	Stephanie	Page		56	56	45	55	21	12	12
47		11	42	Michelle	Robertson		8	8	48	40	45	52	52
48		54	43	Benjamin	Young		34	34	60	5	25	42	42
49		86	44	Michele	Miller		55	55	42	39	18	40	40
50		61	45	Elizabeth	Hart		41	41	20	17	34	47	47
51		34	46	Nicole	Hernandez		25	25	21	43	48	54	54
52		58	47	Donald	Klein		38	38	34	14	32	46	46
53		56	48	Kayla	Hanson		37	37	18	29	39	50	50
54		46	49	Sarah	Davidson		31	31	12	54	57	59	59
55		62	50	Deanna	Velazquez		42	42	56	12	31	45	45
56		78	51	Elizabeth	Jenkins		49	49	26	18	35	48	48
57		59	52	Vickie	Kelly		39	39	32	60	60	31	31
58		85	53	Mark	Whitaker		54	54	58	35	43	23	23
59		71	54	Natasha	Vasquez		45	45	55	42	47	25	25
60		80	55	Patricia	Cruz		51	51	11	45	49	55	55
61		27	56	Steven	Smith		22	22	54	57	58	60	60
62		69	57	Richard	Hickman		44	44	22	49	52	56	56
63		60	58	Michelle	Sanders		40	40	50	41	46	53	53
64		79	59	Robert	Martin		50	50	39	50	53	57	57
65		99	60	Sara	Johns		60	60	28	53	56	58	58
66
Overall

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G6:BD67	Expression	=(RANK(G6,$G6:$BD6)+COUNTIFS($G6:G6,G6)-1)<=$E$4	text	NO

 

[Kaper]

After seeing the screenshot above I noticed that largest results are to be ignored not smallest So the idea could remain the same but adjusted for checking LARGE not SMALL, etc.