If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Simply Array Formula?
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? |
#3
|
|||
|
|||
Simply Array Formula?
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? |
#4
|
|||
|
|||
Simply Array Formula?
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? |
#5
|
|||
|
|||
Simply Array Formula?
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? . |
#6
|
|||
|
|||
Simply Array Formula?
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? . |
#7
|
|||
|
|||
Simply Array Formula?
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? |
Thread Tools | |
Display Modes | |
|
|