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

 

[yogeshmaney]

chief, use it for condiional formatting !

 

[Aladin Akyurek]

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

Select E2:N2.
Fire up CF.
Invoke the following formula:

=(RANK(E$2,$E$2:$N$2)+COUNTIF($E$2:E$2,E$2)-1)<=3

Choose the desired formatting.

 

[pigsfoot]

That is great, works very well. Thank you

Just one more question, is there a way to copy to CF'ing to other rows with the formula increasing by row number. I have a total of 60 entries but i don't really want write 60 seperate conditional formats.

i.e. for every new row the values are increased to match the row number

I have tried to expand the CF range but the forumla didn't increase the row numbers.

Thanks...

 

[Aladin Akyurek]

That is great, works very well. Thank you

Just one more question, is there a way to copy to CF'ing to other rows with the formula increasing by row number. I have a total of 60 entries but i don't really want write 60 seperate conditional formats.

i.e. for every new row the values are increased to match the row number

I have tried to expand the CF range but the forumla didn't increase the row numbers.

Thanks...

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BACKGROUND: #00b0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 width=64 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BACKGROUND: #00b0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 width=64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BACKGROUND: #00b0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 width=64 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>23</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #00b0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 align=right>56</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>56</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #00b0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 align=right>70</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #00b0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 align=right>60</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD></TR></TBODY></TABLE>

Modify the CF formula as:

=(RANK(E2,$E2:$N2)+COUNTIF($E2:E2,E2)-1)<=3

 

[MikeWx]

pigsfoot,

It doesn't look like Conditional Formatting allows array formulas, so I had to add a helper row under your data in F2:L2. The formula in F3 is

=SUM(1*(F2<$F$2:$N$2))+1+IF(COLUMN(F2)-COLUMN($F$2)=0,0,SUM(1*(F2=OFFSET($F$2,0,0,1,INDEX(COLUMN(F2)-COLUMN($F$2)+1,1)-1))))

The above formula is copied across to N3. You can hide the row or change it's font color to white if you don't want it to appear.

Select F2 through N2 and in Conditional Formatting, select Use a formula to determine which cells to format. Below the label Format values where this formula is true, type

=OFFSET(F2,1,0)<=3

The formula for Cell F3 came from a Microsoft post on how to get data ranked with unique rank values, even when there are duplicates.

 

[MikeWx]

I think I found a formula that doesn't need an array for the Conditional Formatting block. The following formula works without having the helper row.

Below the label Format values where this formula is true, type:

=(RANK(F2,$F$2:$N$2)-1)+1+IF(COLUMN(F2)-COLUMN($F$2)=0,0,SUM(1*(F2=OFFSET($F$2,0,0,1,INDEX(COLUMN(F2)-COLUMN($F$2)+1,1)-1))))<=3

 

[T. Valko]

pigsfoot,

It doesn't look like Conditional Formatting allows array formulas.

You can use array formulas in CF.

You just don't need to use the array enter key combo (CSE). Just type in the formula and Enter will do.

 

[snoopyhr]

I am taking a guess here , is this what you want ?

Excel Workbook
	B
1	Some number
2	27
3	15
4	14
5	25
6	28
7	48
8	36
9	22
10	10
11	13
12	12
13	22
14	33
15	29
16	33
17	23
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7	1. / Formula is =B2>=LARGE($B$2:$B$17,3)	Abc

 

[Aladin Akyurek]

I am taking a guess here , is this what you want ?

Excel Workbook
	B
1	Some number
2	27
3	15
4	14
5	25
6	28
7	48
8	36
9	22
10	10
11	13
12	12
13	22
14	33
15	29
16	33
17	23
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7	1. / Formula is =B2>=LARGE($B$2:$B$17,3)	Abc

Why guess? See post #12 (a solution), post #13 displaying OP's reaction to the solution, and post #14 that forwards the requested extension.