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]

Hi,

You can try selecting each of those cells whilst holding down the CTRL key and then naming them, through INSERT|NAME|DEFINE, eg. Array

Then changing the formula to =Large(Array,1)

 

[JasmineL]

Thank you for your response. Unfortunately, it didn't work.

 

[Greg Truby]

You might have to take a two step approach. Use NBVC's solution to create three cells on the WS that have your 1st, 2nd and 3rd place options then set your conditional formats to compare to those fixed cells.

[Nice memory NBVC, I could not seem to remember how to pass a non-contig range in as an array this morning. Need more coffee...]

 

[Domenic]

For the highest score, try...

=(C6<>"")*(C6=LARGE(IF(MOD(ROW(C$6:C$20)-ROW(C$6),2)=0,C$6:C$20),1))

For the second and third highest scores, change the formula accordingly. However, I don't think this will give you the correct results when you have tie scores.

Hope this helps!

 

[JasmineL]

Bummer...sounds like it will have to be a manual process...bummer. I have to use this template file for 14 reports.

 

[barry houdini]

For the highest score, try...

=(C6<>"")*(C6=LARGE(IF(MOD(ROW(C$6:C$20)-ROW(C$6),2)=0,C$6:C$20),1))

For the second and third highest scores, change the formula accordingly.

Hope this helps!

Domenic,

Presumably the conditional formatting should also only apply to even rows so if you want to apply it to a whole range you might need something like

=(C6<>"")*(MOD(ROW()-ROW(C$6),2)=0)*(C6=LARGE(IF(MOD(ROW(C$6:C$20)-ROW(C$6),2)=0,C$6:C$20),1))

edit: sorry altered formula

 

[NBVC]

Thank you for your response. Unfortunately, it didn't work.

Hi Jasmine,

I tested it and my approach should work if you do this.

From one of the cells you enter you input number, invoke the conditional formatting dialogue, then select formula is from the drop down. Enter this: =A1=LARGE(Array,1), where Array is the the named range you created after selecting the individual cells with the CTRL key depressed. And A1 is the cell you are in (which is the input cell). Then add similar conditions for 2nd and 3rd highest and choose your formats, Then simply copy and paste special the format to other cells as required.

Let me know.

 

[JasmineL]

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.

 

[Domenic]

Domenic,

Presumably the conditional formatting should also only apply to even rows so if you want to apply it to a whole range you might need something like

=(C6<>"")*(MOD(ROW()-ROW(C$6),2)=0)*(C6=LARGE(IF(MOD(ROW(C$6:C$20)-ROW(C$6),2)=0,C$6:C$20),1))

edit: sorry altered formula

Thanks Barry! You're definitely right. But 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=