Conditional formatting on highest values in row

Cross-posted at https://www.mrexcel.com/board/thread...cells.1264619/

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

PS: I've added an example workbook with 7 races complete, which should lead to 2 highest scores formatted to strikethrough

Administrative Note:

Welcome to the forum.

Is your forum profile showing ONLY the oldest Excel PRODUCT that you need this to work for? 'Latest' could be so many different things ...

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Then I'll remind you what I actually asked:

Is your forum profile showing ONLY the oldest Excel PRODUCT that you need this to work for?

Based on what you have just said, this is not the case. Please adjust your profile accordingly with the oldest version before anyone starts suggesting solutions that might not work on all platforms.

Hi Ali,
thanks a lot for your support. I have checked now with everyone who might be working on it and averting is using MS365 in version 2407. so a solution for MS365 would be great. Thanks a lot!!

This works for highlighting the two largest numbers.
1. Add two helper columns populated using:

=IF(COUNTIFS($G6:$M6,MAX($G6:$M6))>1,AGGREGATE(15,6,(COLUMN($G6:$M6)-COLUMN($F6))/($G6:$M6=LARGE($G6:$M6,COLUMNS($BG6:BG6))),COLUMNS($BG6:BG6)),AGGREGATE(15,6,(COLUMN($G6:$M6)-COLUMN($F6))/($G6:$M6=LARGE($G6:$M6,COLUMNS($BG6:BG6))),1))

2. Apply the following conditional formatting rule to columns G:M

=OR(COLUMNS($G6:G6)=$BG6,COLUMNS($G6:G6)=$BH6)

Let us know if you have any questions.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
For new members: you will not be able to post a full URL, so remove the https://www. section and add one or two breaks to the rest of the address. A Moderator or Admin will then fix the link for you.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I have added the link your post above since you new. Please take the time to review our rules. There aren't many, and they are all important.