Simply Array Formula?

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.


"Going Crazy with excel" wrote:

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?

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"

"Mike H" wrote:

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" wrote:

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?

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)
--
David Biddulph


"Going Crazy with excel"
wrote in message ...
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"

"Mike H" wrote:

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" wrote:

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?

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

"David Biddulph" wrote:

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)
--
David Biddulph


"Going Crazy with excel"
wrote in message ...
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"

"Mike H" wrote:

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" wrote:

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?


.

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...... CFNew RuleUse a
Formula

You must make 3 rules for the selection.

ver 2003...........FormatCFCondition1Formula is

Add 2 more conditions.



Gord Dibben MS Excel MVP


On Mon, 31 May 2010 10:21:01 -0700, Going Crazy with excel
wrote:

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

"David Biddulph" wrote:

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)
--
David Biddulph


"Going Crazy with excel"
wrote in message ...
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"

"Mike H" wrote:

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" wrote:

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

"Mike H" wrote:

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" wrote:

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?