Formatting multiple values

Hi,
I have been using conditional formatting (see formulas below)to highlight the 3 largest values in a column; the largest =red, 2nd largest=green & 3rd largest =blue. A problem arises if there are multiple entries of values. For example, if the largest value appears numerous times Excel will highlight them all in red & ignore the second & third place values. If two cells contain the largest value, it will highlight those in red, one cell with the second largest value, & ignore everything else. How can I make ALL cells with the largest value=red, ALL second largest=green, ALL third largest=blue?
And just out of curiosity, if the largest & 2nd largest values each appear once, and the 3rd largest appears several times, what logic does 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)

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)

--
Cheers,

V.


"fujimi-cho" wrote:

>
> Hi,
> I have been using conditional formatting (see formulas below)to
> highlight the 3 largest values in a column; the largest =red, 2nd
> largest=green & 3rd largest =blue. A problem arises if there are
> multiple entries of values. For example, if the largest value appears
> numerous times Excel will highlight them all in red & ignore the second
> & third place values. If two cells contain the largest value, it will
> highlight those in red, one cell with the second largest value, &
> ignore everything else. How can I make ALL cells with the largest
> value=red, ALL second largest=green, ALL third largest=blue?
> And just out of curiosity, if the largest & 2nd largest values each
> appear once, and the 3rd largest appears several times, what logic does
> 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)
>
>
> --
> fujimi-cho
> ------------------------------------------------------------------------
> fujimi-cho's Profile: http://www.excelforum.com/member.php...o&userid=14759
> View this thread: http://www.excelforum.com/showthread...hreadid=431051
>
>