Conditional formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barney - 30 Dec 2005 10:57 GMT

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

Reply to this Message

Dirk Van de moortel - 30 Dec 2005 11:37 GMT

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

Supposing the numbers are in column A, then you can do this:

If
Cell Value is
greater than or equal to
=MEDIAN(A:A)
then
highlight

Dirk Vdm

Reply to this Message

Bob Phillips - 30 Dec 2005 13:00 GMT

I answered in other thread, but copied here for info

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

Signature

HTH

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

Reply to this Message

Dirk Van de moortel - 30 Dec 2005 13:39 GMT

> I answered in other thread, but copied here for info
>
[quoted text clipped - 13 lines]
> OK
> OK

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

Dirk Vdm

Reply to this Message