less than MAX, more than MIN

[fujimi-cho]

I want to use conditional formatting to highlight the 3 largest and/or 3 smallest numbers in a column. I know how to get the MIN & the MAX, but can't figure out a formula to find the 2nd & 3rd place values.

[pinmaster]

Hi
=LARGE(A1:D10,2)
=LARGE(A1:D10,3)

HTH
JG

[fujimi-cho]

Thanks Pinmaster, for helping a beginner. That worked fine in most instances, but not in others. The problem seems to be repeating numbers. I want the cell with the largest number to be red, the second largest green & the third largest blue. If more than one cell has a given number, I would like them all to be red, green or blue as the case may be. In the first column below, 2 of the "81" cells are red, the other is blue. In the second column "121" is red as it should be, but the first "107" is green & the second is blue.

81 121
79 86
77 92
66 0
81 0
81 107
76 79
75 107
77

[AlfD]

Hi!

Put the =large("Range",1) (or 2 or 3) in three cells.

Now use conditional formatting on the list of data to colour the cells when they equal these new cells, no matter how many duplicates you find.

You have only 3 conditions to use. If 3 isn't enough (because you want the 3 largest and 3 smallest) then I suggest you do large and small in different columns as a work-around.

Alf