How to use conditional formatting to highlight top 3 ranked scores - breaks when 2 scores are the same :-(

[drjarmin]

Hi All

I thought this would be easier than it is, or maybe I'm being a bit dumb. I have a list of test scores from 20 students (scored from 1 - 100) and I would like to highlight the cells for the top 3 students (ie Gold, Silver Bronze). I'm using the Rank.EQ function to show 1, 2, 3 etc.

I can put in a conditional formatting rule to highlight the bottom 1 (ie ranked 1) score, bottom 2 (ie second place) and bottom 3 (3rd place) and if I put the rules in cascading order it works.

However, it falls down if two or more students in the top 3 have the same scores, this doesn't work.

For example:

Student A: Score 90: Rank 1 works: Gold awarded - conditional formatting will apply for lowest value)
Student B: Score 85: Rank 2 works: Silver awarded - conditional formatting will apply for second lowest value)
Student C: Score 85: Rank 2 works: Silver awarded - conditional formatting will apply for second lowest value)
Student D: Score 80: Rank 4 FAIL: No bronze awarded as the conditional formatting will treat this as the fourth lowest value.

Is there any way to achieve this without resorting to VBA? I tried also using the Large() and Small() functions but end up with the same issue.

Thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want. I have used different colours but try putting the rules in the order shown in your CF dialog & mark the 'Stop If True' box for the top 2 at least

Excel Workbook
	B
2	90
3	60
4	85
5	26
6	80
7	85
8	80
9	85
CF Top 3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =COUNT(IF(B$2:B$9>B2,IF(MATCH(B$2:B$9,B$2:B$9,0)=ROW(B$2:B$9)-ROW(B$2)+1,1,NA())))+1<=1	Abc
B2	2. / Formula is =COUNT(IF(B$2:B$9>B2,IF(MATCH(B$2:B$9,B$2:B$9,0)=ROW(B$2:B$9)-ROW(B$2)+1,1,NA())))+1<=2	Abc
B2	3. / Formula is =COUNT(IF(B$2:B$9>B2,IF(MATCH(B$2:B$9,B$2:B$9,0)=ROW(B$2:B$9)-ROW(B$2)+1,1,NA())))+1<=3	Abc

 

[drjarmin]

Thank you! That does work for a row, unfortunately I should have been more specific as my data is across columns. I tried to transpose your formulae (using varients of the below) but just isn't working:


=COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$F2:$M2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=1
=COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$F2:$M2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=2
=COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$F2:$M2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=3

 

[Peter_SSs]

I tried to transpose your formulae (using varients of the below) but just isn't working:


=COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$F2:$M2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=1

The error is those red values. Should be
=COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=1

Excel Workbook
	B	C	D	E	F	G	H	I
2	90	60	85	26	80	85	80	85
CF Top 3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=1	Abc
B2	2. / Formula is =COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=2	Abc
B2	3. / Formula is =COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=3	Abc

 

[drjarmin]

Ah, thank you! Its kinda working but in my original post its the ranking I'm trying to conditional format (ie 1st, 2nd, 3rd) which I'm generating using the Rank.Eq() function:

Score		2	90	60	85	26	80	85	80	85
Rank		3	1	7	2	8	5	2	5	2

<colgroup><col><col><col span="9"></colgroup><tbody>
</tbody>

The error is those red values. Should be
=COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=1

CF Top 3

*	B	C	D	E	F	G	H	I
2	90	60	85	26	80	85	80	85

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"></colgroup><tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format B2 1. / Formula is =COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=1 Abc B2 2. / Formula is =COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=2 Abc B2 3. / Formula is =COUNT(IF($B2:$I2>B2,IF(MATCH($B2:$I2,$B2:$I2,0)=COLUMN($B2:$I2)-COLUMN($B2)+1,1,NA())))+1<=3 Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[drjarmin]

Sorry to ask, but do you mind to explain this construct to me? I'm trying to break down your solution to figure out how it works.

$B2:$I2>B2

I've put that into a sheet for each cell (B2 - I2) and it always returns false. But this is the test condition in your first IF statement. Not sure what comparing a range to a single cell value actually means and I've guessing this evaluates differently if used for conditional formatting (ie it takes the current cell from that range for the comparison?)?

Thanks

 

[Peter_SSs]

.. its the ranking I'm trying to conditional format (ie 1st, 2nd, 3rd)

Like this?

Excel Workbook
	B	C	D	E	F	G	H	I
2	90	60	85	26	80	85	80	85
3	1	7	2	8	5	2	5	2
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	1. / Formula is =COUNT(IF($B3:$I3<=1	Abc
B3	2. / Formula is =COUNT(IF($B3:$I3<=2	Abc
B3	3. / Formula is =COUNT(IF($B3:$I3<=3	Abc

 

[drjarmin]

Yes perfect!!!

Do you mind explaining this construct from the first IF statement ... $B3:$I3<B3. I'm not familiar with comparing a range to a cell. I'm guessing if used with conditional formatting it acts as a loop? I can't replicate it to return any values other than false in a worksheet.

 

[Peter_SSs]

Do you mind explaining ...

I'll try.
When entered in Conditional Formatting Excel interprets formulas , if possible, as array formulas. So, if we consider the blue condition formula from post #7 as it would apply to cell H3, it would be

{=COUNT(IF($B3:$I3<H3,IF(MATCH($B3:$I3,$B3:$I3,0)=COLUMN($B3:$I3)-COLUMN($B3)+1,1,NA())))+1<=3}

Since this is an array formula, the blue bit checks every cell to see if it is the first occurrence of its value in the range. If it is we put a 1, otherwise the error value NA(). The result of that is
{1,1,1,1,1,#N/A,#N/A,#N/A}}


The red bit checks every cell in the range to see if it is less than H3 (5). The result of that check would be
{True, False, True, False, False, True, False, True}

That is, the 1st, 3rd, 6th & 8th cells meet the condition of being less than H3.


The result of combining those 2 arrays is
{1, False, 1, False, False, #N/A,#N/A,#N/A}

We now COUNT the numerical values in that array and get 2. That is, we have found 2 distinct values in the range that are less than H3.
Now add 1 to that result and see if it is <=3, which it is for H3 so it gets coloured blue.

 

[drjarmin]

Thanks for taking the time for such a detailed explanation. Makes sense and it works at the worksheet level if I change it to an array formula {=$B2:$I2>B2}.