Prev: Exporting the values to excel error
Next: how to turn off standard toolbar, formatting... Microsoft Excel 20
From: Going Crazy with excel on 31 May 2010 11:11
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?
From: Mike H on 31 May 2010 11:21
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?
From: Going Crazy with excel on 31 May 2010 11:44
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?
From: "David Biddulph" groups [at] on 31 May 2010 11:58
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" <GoingCrazywithexcel(a)discussions.microsoft.com>
wrote in message news:07AE9795-F887-4B59-B9FE-7FFDFDC6372A(a)microsoft.com...
> 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?


From: Going Crazy with excel on 31 May 2010 13:21
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" <GoingCrazywithexcel(a)discussions.microsoft.com>
> wrote in message news:07AE9795-F887-4B59-B9FE-7FFDFDC6372A(a)microsoft.com...
> > 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?
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Exporting the values to excel error
Next: how to turn off standard toolbar, formatting... Microsoft Excel 20