Using Large/Small function

[JasmineL]

I'm using the Large function in conditional formatting to determine the top 3 scores in a range of numbers selected and coloring them based on their 1, 2, 3 rank. The conditional formatting works when the cells selected are in the same grouping [=LARGE(C$6:C$13,1)]; however, when I try to use the same formula but changing the cells to individually selected cells [=large(C$6,C$8,C$10,C$12,C$14,C$16,C$18,C$20,1)]...thoughts?

 

[NBVC]

Strange...when I tried to assign the array earlier, it didn't recogize...I renamed the range and instead of using the formula drop down in conditional formatting, I used the cell value equal to...and it worked.

Correct, you can do it thru' cell value is and Equal to and =Large(Array,1) as well. I just like doing it with formula is...matter of taste

 

[barry houdini]

....since the OP wants to highlight the second and third highest scores as well, this won't give the correct results when there are ties. Probably best to adopt Aladin's Top N list described in the following link...

http://www.mrexcel.com/board2/viewtopic.php?t=69970&highlight=

That was my thought originally but now I don't see why it wouldn't work. If you use that formula as 1st condition then a second condition with the 2nd argument of LARGE set to 2 and then 3rd condition with 3 then it essentially works like a standard ranking would.

If there is a tie for 1st then two cells are highlighted red but nothing highlighted for the second condition

 

[JasmineL]

I want the ties to color as well...in other words, if the numbers are 100, 50, 50, 25. I want the 100 colored one way, the two 50s colored another way, and the 25 colored a third way.

Here's what is working:

Select the range and assign a name (Array1 does not get recognized, but ArrayA does).
Choose the range for ranking, select conditional formatting.
Choose Cell Value Is...Equal To...=Large(ArrayA,1)
Repeat for conditions 2 and 3.

It's pretty cool...the only time consuming part is having to define all of the names.

Thanks for all the help.

 

[Domenic]

That was my thought originally but now I don't see why it wouldn't work. If you use that formula as 1st condition then a second condition with the 2nd argument of LARGE set to 2 and then 3rd condition with 3 then it essentially works like a standard ranking would.

If there is a tie for 1st then two cells are highlighted red but nothing highlighted for the second condition

For some reason, I was thinking in terms of 'consecutive ranking'...