Format top 3 values, exclude duplicates

[pigsfoot]

Hi,

I have a spreadsheet of scores and i want to hightlight the top 3 scores in red.

However sometimes the scores are duplicated so there maybe 2 second places for example.

I have tried =Large(A1:A5,3) but if i have the following scores "1,2,2,3,4" both the seconds are both highlighted along with the 3rd and 4th i.e. 4 in total are highlighted.

Is there are way to ONLY hightlight the first 3 top scores

Thanks...Rod

 

[Aladin Akyurek]

What is the result list if we have:

20
30
40
40
25

 

[pigsfoot]

Hi Aladin,

Not sure what you mean but if they were the resuts i woudl want to highlight score

40
40
30

As they are the top 3 hightest scores

It dosn't really matter what the top scores are, even if they are all the same as long as ONLY the top three are highlighted.

They could even be

40
40
40
40
40

I would still only want to highlight 3 of the 40 scores.

Thanks

 

[yogeshmaney]

What if we use Small Function instead

 

[pigsfoot]

Hi,

"Small" won't really work as the number of matches will increase but only the top 3 will ever need highlighting.

Thanks

 

[Aladin Akyurek]

Hi Aladin,

Not sure what you mean but if they were the resuts i woudl want to highlight score

40
40
30

As they are the top 3 hightest scores

It dosn't really matter what the top scores are, even if they are all the same as long as ONLY the top three are highlighted.

They could even be

40
40
40
40
40

I would still only want to highlight 3 of the 40 scores.

Thanks

In D2 enter and copy down:

=IF(ROWS($D$2:D2)<=3,LARGE($A$1:$A$5,ROWS($D$2:D2)),"")

 

[yogeshmaney]

=INDEX(your Range,LARGE(B2:B6,your rank number),1)

 

[taurean]

See if this works for you:
=AND(RANK($A1,$A$1:$A$5)<4,COUNTIF($A$1:$A1,$A1)<2)

 

[pigsfoot]

Thanks for your help everyone but maybe i havn't made myself very clear.

What i want to do i highlight the top three numbers, i.e. change font colour to Red, make it Bold, strikethrough, anything really to make them stand out. All three would be very helpfull.

I do not need a sum of the of the scores i simply what to highlight them.

( i have another formula that is adding scores together miinus these three discards )

The example i gave in my original post was an example to simplfy things, my scores acutually run from E2 to N2. Only one competition is run per month and we are on month 7 so far so only feilds F2 to L2 are completed with others to follow. As and when others are filled in i want it to automatically update the top three highest scores.

Below is an example of one line
F2 G2 H2 I2 J2 K2 L2
35 2 35 1 2 1 1

So what i want to happen is fields F2,H2 & G2 to be highlighted to stand out some how.

I hope this makes it clearer

Thanks

 

[taurean]

I thought I understood you correctly. You have stated you want to exclude duplicates. But 35 is duplicate. Now I am confused. Did you try my suggestion?