Formatting largest cells

[Barney]

Hi,
In a row of 6 numbers I want to highlight the 3 largest numbers. If the row
contains 7 or 8 numbers, I want to highlight the 4 largest of those numbers.
If the row contains 9 or 10 numbers, I want to highlight the 5 largest of
those numbers. Any suggestions on how to make this work?
Thanks in advance, Barney

 

[Bob Phillips]

Hi Barney,

Conditional Formatting will do that.

Let's start by assuming the data is in row 21, then

Select row 21
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=ISNUMBER(MATCH(A21,LARGE($21:$21,ROW(INDIRECT("1:"&INT((COUNTA($21:$21)+1)/
2)))),0))
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dirk Van de moortel]

Hi Barney,

Conditional Formatting will do that.

Let's start by assuming the data is in row 21, then

Select row 21
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=ISNUMBER(MATCH(A21,LARGE($21:$21,ROW(INDIRECT("1:"&INT((COUNTA($21:$21)+1)/
2)))),0))
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Select row 21
Menu Format, Conditional formatting
Cell Value is
greater than or equal to
=MEDIAN(21:21)


Dirk Vdm