Simply Array Formula?

  • Thread starter Thread starter Going Crazy with excel
  • Start date Start date
G

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?
 
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.
 
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"
 
Confused. Really dumb today.
"select" meaning "highlite"?
Use the "CF" formula? CF meaning?
Formula itself entered on the formula bar?
 
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
 
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?
 
Back
Top