Formatting multiple values

[fujimi-cho]

Hi,
I have been using conditional formatting (see formulas below)t
highlight the 3 largest values in a column; the largest =red, 2n
largest=green & 3rd largest =blue. A problem arises if there ar
multiple entries of values. For example, if the largest value appear
numerous times Excel will highlight them all in red & ignore the secon
& third place values. If two cells contain the largest value, it wil
highlight those in red, one cell with the second largest value,
ignore everything else. How can I make ALL cells with the larges
value=red, ALL second largest=green, ALL third largest=blue?
And just out of curiosity, if the largest & 2nd largest values eac
appear once, and the 3rd largest appears several times, what logic doe
Excel use to determine which of the 3rd place cells are highlighted?

=MAX(B$5:B$40)
=LARGE(B$5:B$40,2)
=LARGE(B$5:B$40,3

 

[Guest]

Hi,
Take this example: {1,2,3,4,5,1,3,4,4}
I would say that Excel will highlight the cell containing 5 in red, and the
cells containing 4 in green and none in blue because, the 2nd largest, 3rd
largest and 4th largest cells (excel understanding) are those which contain
the number 4.
However, you could these formulas instead of yours:
=LARGE($B$5:$B$40,1)
=LARGE($B$5:$B$40,COUNTIF($B$5:$B$40,LARGE($B$5:$B$40,1))+1)
=LARGE($B$5:$B$40,COUNTIF($B$5:$B$40,">="&LARGE($B$5:$B$40,2))+1)