Duplicates in rank conditionally formatting

**bexlee** · 02-11-2022, 07:34 AM

Hi I have to colour cells green which are the top 6 scores. When a person has 2 scores the same it highlights 7 cells and when they have 3 scores the same it highlights 8 cells. I would like it to highlight 6 cells even if the scores for example were 100, 99, 99, 95,99, 95,94,100,92,91, I only want the two 100's, the three 99's and the 95 highlighted.
Thank you in advance I am very new to this and self taught I learn by googling and doing. Rebecca

**AliGW** · 02-11-2022, 07:35 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) 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 version is for Mac, please also state this. Thanks.

**Glenn Kennedy** · 02-11-2022, 07:44 AM

This will return 6, even if there is a "tie" for 6th and 7th positions...

=ISNUMBER(MATCH(A1+ROW(A1)/10^6,AGGREGATE(14,6,$A$1:$A$8+ROW($A$1:$A$8)/10^6,{1,2,3,4,5,6}),0))

**bexlee** · 02-11-2022, 08:04 AM

sorry for being so basic but I want it to colour the cells of the corresponding numbers but only colour the top 6 scores even if there are duplicates is this possible?