Conditional formatting for specific number of cells

dcobe

New Member
Joined
Sep 20, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • Screenshot 2.png
    Screenshot 2.png
    157.2 KB · Views: 16

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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:
Excel Formula:
=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)

1726928810177.png
 
Upvote 0
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:
Excel Formula:
=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:
Excel Formula:
=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 :LOL::ROFLMAO:.
 
Last edited:
Upvote 0
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:

Excel Formula:
=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:
Excel Formula:
=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)
Excel Formula:
=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:
Excel Formula:
=LET(r,$G6:$BD6,c,INT(COUNT(r)/3),q,SMALL(r,c),AND((COUNTIF($G6:G6,q)+COUNTIF(r,"<"&q))<=c,G6<=q))


1726937602282.png
 
Upvote 0
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:

Excel Formula:
=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:
Excel Formula:
=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)
Excel Formula:
=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:
Excel Formula:
=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.
 
Upvote 0
Another option
formatting-sample.xlsx
ABCDEFGHIJKLMN
1
2
3Overall Ranking
4Streicher2
5LycraRankVornameNachnameRace 1Race 2Race 3Race 4Race 5Race 6Race 78
651AndrewMays33403222
7492BrandonDiaz3333137444
873MichaelEvans4414381799
924BrandiHarrington22196264343
1015StevenHill112356224141
11146JenniferMorse111144251166
12927BenjaminMiller5757434333
13138JoseBarnes1010428382020
14189KimberlyShepard131352321588
154810EdwardRojas32324915655
167211DiamondCarter464671353232
178412AlexanderMcgrath5353411111
181713TammyLewis12123558231313
191014TinaAlvarado77259593030
207615CaseyAdams484819281616
212616ChristineChapman2121910291717
22917SandraCastillo66852552929
234418JerryBenitez3030626123737
244219RebeccaHodges28282447201111
252520ElizabethGates20201716331818
262321MelissaJimenez18182737163939
278222EmmaBecker525251973333
281223MariahJohnson992934422222
291924KimWilson14145931143838
309525JesusLucero585837271377
315526JacquelineFuller3636162083434
322427JamesMartin19193822361919
333528NoahLittle26263644191010
346529AmberReynolds4343472241414
352230SamanthaFigueroa17171551542828
365331BrendaSavage3535518271515
37832RhondaPerry554648512727
382833MelissaChung23231036442424
392134JasonJohnson16163023374949
407335JasonJoseph47473124103636
414036LuisAnderson2727333415151
429737JamesKim5959332193535
432038RachelSmith15155346502626
443039ChristopherWest24245711304444
454340KennethJackson29292530402121
469041StephaniePage56564555211212
471142MichelleRobertson884840455252
485443BenjaminYoung3434605254242
498644MicheleMiller55554239184040
506145ElizabethHart41412017344747
513446NicoleHernandez25252143485454
525847DonaldKlein38383414324646
535648KaylaHanson37371829395050
544649SarahDavidson31311254575959
556250DeannaVelazquez42425612314545
567851ElizabethJenkins49492618354848
575952VickieKelly39393260603131
588553MarkWhitaker54545835432323
597154NatashaVasquez45455542472525
608055PatriciaCruz51511145495555
612756StevenSmith22225457586060
626957RichardHickman44442249525656
636058MichelleSanders40405041465353
647959RobertMartin50503950535757
659960SaraJohns60602853565858
66
Overall
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G6:BD67Expression=(RANK(G6,$G6:$BD6)+COUNTIFS($G6:G6,G6)-1)<=$E$4textNO
 
Upvote 0
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.
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Conditional formatting on highest values in row
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,224,465
Messages
6,178,822
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top