Simply Array Formula?

[Going Crazy with excel]

I have a column of numbers. Looking for an array formula that will identify
and highlite the "highest" three values. Does such an animal exist?

 

[Mike H]

Hi,

Here's 2 ways and both assume your data are in A1:A20.

Put this in a cell and drag down to get the 3 largest numbers
=LARGE($A$1:$A$20,ROW(A1))

Or with conditional formatting. Select the range A1:A20

and apply a conditional format of
=A1=LARGE($A$1:$A$20,1)
Set your colour

Add a second CF of
=A1=LARGE($A$1:$A$20,2)

And a third of
=A1=LARGE($A$1:$A$20,3)





--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Going Crazy with excel]

Think you are on the right track, but I am dumb. Probably want to go with
the conditional formula. Numbers are in column "B" running from B2 thru B21.
Where do I put the formula? In cell "B2"

 

[David Biddulph]

Yes, but of course you'd have to select the range B2:B21 and use the CF
"formula is" of
=B2=LARGE($B$2:$B$21,1)
=B2=LARGE($B$2:$B$21,2)
=B2=LARGE($B$2:$B$21,3)

 

[Going Crazy with excel]

Confused. Really dumb today.
"select" meaning "highlite"?
Use the "CF" formula? CF meaning?
Formula itself entered on the formula bar?

 

[Gord Dibben]

Select means click on B2 and drag down to B21 which selects all those cells.

CF means Conditional Formatting

The formulas are to be entered into........ver 2007...... CF>New Rule>Usea
Formula

You must make 3 rules for the selection.

ver 2003...........Format>CF>Condition1>Formula is

Add 2 more conditions.



Gord Dibben MS Excel MVP

 

[jkiser]

Mike H... just snooping around and stopped on this post to learn something.
This is really a neat function.

Question... when looking in the Excel help about the LARGE Function. It
explained the function but didn't go so far as to show the substitution of
Row(A1) for "k"). Where did you learn to do that?

If I wanted to find a reference book/site on the use of higher-order excel
formulas where would I start?