# Using Large/Small function

#### JasmineL

##### Active Member
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?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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)

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

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...]

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!

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

Domenic said:
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

JasmineL said:
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.

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.

barry houdini said:
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=

Replies
5
Views
88
Replies
2
Views
177
Replies
9
Views
149
Replies
2
Views
191
Replies
0
Views
70

1,196,080
Messages
6,013,315
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back