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)


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

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