need slight adjustment of conditional format

[FreakyGirl]

Hello

Im using a conditional formula that highlights the top 6 scores of a player.

the scoring section with conditional is a named range $O$11:$AB$843

=AND(SUMPRODUCT(--ISNUMBER($O11:$AB11))>=6,RANK(O11,$O11:$AB11)+SUMPRODUCT((O11=$O11:$AB11)*(P$10>$O$10:$AB$10))<7)

The formula is giving diff results, its not always top 6. Some players will have less than 6 highlighted, some will have 6 & some will have 7!

Im thinking its because of the 7th highest score being a duplicate of the 6th largest but i dont know how to adjust formula.

for example:
the 1st 5 players are listed starting in H11. the score grid is O11:AB11 & top 6 scores are in AD11

B_A_B_E top 6 scores: 8 8 7 7 6 6 - last 6 is not highlighted.
7th largest is a 6

__ANN__ top 6 scores: 8 8 7 7 6 6 - last 6 is not highlighted.
7th largest is a 6

SoWicked top 6 scores are: 8 8 8 7 5 5 - last 5 is not highlighted
7th largest is a 5

angelxo2001 top 6 scores are: 8 7 7 7 6 6 - all 6 scores are highlighted
7th largest is a 5 which doesnt match the 6th largest which is why this one is correct!!!

only 4 scores highlighted for this player
Chandy top 6 scores are: 8 8 7 6 5 5 - last two 5s are not highlighted
7th largest is a 5 as well as the 8th largest which is a tie.

this player has 7 scores highlighted
jr_27_2001 top 6 scores are: 6 6 6 5 5 4 but 7 scores are highlighted: 6 6 6 5 5 4 & 4.
7th largest is a 4 which is a tie with the 6th highest.

i have attached the workbook.

Thanks in advance for any help,
FreakyGirl

 

[GraH - Guido]

perhaps formula works like this...
=RANK.EQ(O11,$O11:$AB11)<7

EDIT: better, to avoid grey in empty cells.

=AND(O11<>"",RANK.EQ(O11,$O11:$AB11)<7)

 

[FreakyGirl]

Hello

Thanks for your reply. I tried both formulas you suggested & upon hitting apply all the gray highlighting was removed as if no conditional was there at all.

im still working on it though!

FreakyGirl

 

[GraH - Guido]

Strange, seems to working on my end...

 

[FreakyGirl]

hello

im not sure. i do have 2007 & i think that function didnt come out till 2010.

when i open your file, i see no gray highlighting at all. just the grid of scores.

however i was able to get the conditional working correctly using MATCH & COUNTIF with the top 6 scores in CP to CU.

=AND(O11<>"",MATCH(O11,$CP11:$CU11,0),COUNTIF($O11:O11,O11)<=COUNTIF($CP11:$CU11,O11))

file attached.

thanks for your help,
FreakyGirl

 

[GraH - Guido]

hi FreakyGirl, you could also have tried =AND(O11<>"",RANK(O11,$O11:$AB11)<7), I guess.