Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract one numerical value from single cell with multiple values? | Excel Worksheet Functions | |||
How to calculate values in multiple values with multi conditions | Excel Worksheet Functions | |||
Multiple lookup value's | Excel Worksheet Functions | |||
Toggle multiple values in single cell | Excel Worksheet Functions | |||
Multiple If True Values? | Excel Discussion (Misc queries) |