Hello there;

I have a list with values from A1:A2000 from which Iwould like to
retrieve the Top 30 (largest values) in B1:B20?

I think I have seen it done by a nice array-formula the otherday, but
I cannot recreate, nor find it...
Can someone help me out?

Jen

I have a list with values from A1:A2000 from which Iwould like to
retrieve the Top 30 (largest values) in B1:B20?

I think I have seen it done by a nice array-formula the otherday, but
I cannot recreate, nor find it...
Can someone help me out?

Not sure about an array-formula, but if you put this in B1...

=LARGE($A$1:$A$2000,ROW(B1))

and copy it down through B30, it should do what you asked for.

Rick

Hi Rick,
Thanks for you solution. It works good but ...

It's that I just would like to see it performed with 1 formula ... and
enjoying the advantage that you cannot change an array-formula accidentally
by deleting a cell.

Jen

"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a list with values from A1:A2000 from which Iwould like to
retrieve the Top 30 (largest values) in B1:B20?

I think I have seen it done by a nice array-formula the otherday, but
I cannot recreate, nor find it...
Can someone help me out?

Not sure about an array-formula, but if you put this in B1...

=LARGE($A$1:$A$2000,ROW(B1))

and copy it down through B30, it should do what you asked for.

Rick

jen,

You can't have a formula in (say) B1; array or otherwise, that outputs a
value to another cell. You can either go with the solutions you have in both
of your threads or resort to VB which is a bit sledghammer walnutish for what
you are trying to achieve.

Mike

"Jen" wrote:

Hi Rick,
Thanks for you solution. It works good but ...

It's that I just would like to see it performed with 1 formula ... and
enjoying the advantage that you cannot change an array-formula accidentally
by deleting a cell.

Jen

"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a list with values from A1:A2000 from which Iwould like to
retrieve the Top 30 (largest values) in B1:B20?

I think I have seen it done by a nice array-formula the otherday, but
I cannot recreate, nor find it...
Can someone help me out?

Not sure about an array-formula, but if you put this in B1...

=LARGE($A$1:$A$2000,ROW(B1))

and copy it down through B30, it should do what you asked for.

Rick

Oops sorry for the double posting ...
I re-posted because I could not find my origial Q. Sorry for that.
Jen

I like the Sub-stantial walnut crushing sledgehammer ;)

On Jun 30, 10:44 am, Mike H wrote:
jen,

You can't have a formula in (say) B1; array or otherwise, that outputs a
value to another cell. You can either go with the solutions you have in both
of your threads or resort to VB which is a bit sledghammer walnutish for what
you are trying to achieve.

Mike



"Jen" wrote:
Hi Rick,
Thanks for you solution. It works good but ...

It's that I just would like to see it performed with 1 formula ... and
enjoying the advantage that you cannot change an array-formula accidentally
by deleting a cell.

Jen

"Rick Rothstein (MVP - VB)" wrote in
. ..
I have a list with values from A1:A2000 from which Iwould like to
retrieve the Top 30 (largest values) in B1:B20?

I think I have seen it done by a nice array-formula the otherday, but
I cannot recreate, nor find it...
Can someone help me out?

Not sure about an array-formula, but if you put this in B1...

=LARGE($A$1:$A$2000,ROW(B1))

and copy it down through B30, it should do what you asked for.

Rick- Hide quoted text -

- Show quoted text -

Jen,

Thought I'd do a VB solution anyway. This 'borrows' cell f1 to do the
calculations. Ive done it that way because VB doesn't include the 'Large'
function.

Sub stantial_effort_minimal_problem()
For x = 1 To 20
Range("F1").Select
ActiveCell.FormulaR1C1 = "=LARGE(RC[-5]:R[1999]C[-5]," & x & ")"
Cells(x, 2).Value = ActiveCell.Value
Next
End Sub

Mike

"Jen" wrote:

Hi Rick,
Thanks for you solution. It works good but ...

It's that I just would like to see it performed with 1 formula ... and
enjoying the advantage that you cannot change an array-formula accidentally
by deleting a cell.

Jen

"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a list with values from A1:A2000 from which Iwould like to
retrieve the Top 30 (largest values) in B1:B20?

I think I have seen it done by a nice array-formula the otherday, but
I cannot recreate, nor find it...
Can someone help me out?

Not sure about an array-formula, but if you put this in B1...

=LARGE($A$1:$A$2000,ROW(B1))

and copy it down through B30, it should do what you asked for.

Rick

Hello Jen,

Array enter into B1:B30:

=LARGE(A1:A2000,ROW(A1:A30))

Of course can also write ROW(1:30) but the formula would recalc if any
value in row 1:30 would change. This way it only depends on A1:A2000.

Regards,
Bernd

Hi Bernd,
This was xctly the elegant array-formula I was after, thx a lot!!
Jen

On Jun 30, 12:43 pm, Bernd P wrote:
Hello Jen,

Array enter into B1:B30:

=LARGE(A1:A2000,ROW(A1:A30))

Of course can also write ROW(1:30) but the formula would recalc if any
value in row 1:30 would change. This way it only depends on A1:A2000.

Regards,
Bernd

Array enter into B1:B30:

=LARGE(A1:A2000,ROW(A1:A30))

Of course can also write ROW(1:30) but the formula would recalc if any
value in row 1:30 would change. This way it only depends on A1:A2000.

I just got up for the morning, so I may still be a little groggy, but I
don't get that formula to work correctly. I array-entered it in B1 and then
copied it down to B30 and it does not give me the results my formula does
(and the differences are skipped, high numbers in your formula). If I change
the A1:A2000 range to the absolute one $A$1:$A$2000 in your formula, then it
produces the same results as my formula (no skipped high numbers); but then
I don't understand what the array-entry is doing for us with regard to that
second argument. What am I missing here (either in the way I entered the
formula or in my understanding of what the array-entry method is doing)?

Rick

On Sat, 30 Jun 2007 10:30:24 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Array enter into B1:B30:

=LARGE(A1:A2000,ROW(A1:A30))

Of course can also write ROW(1:30) but the formula would recalc if any
value in row 1:30 would change. This way it only depends on A1:A2000.

I just got up for the morning, so I may still be a little groggy, but I
don't get that formula to work correctly. I array-entered it in B1 and then
copied it down to B30 and it does not give me the results my formula does
(and the differences are skipped, high numbers in your formula). If I change
the A1:A2000 range to the absolute one $A$1:$A$2000 in your formula, then it
produces the same results as my formula (no skipped high numbers); but then
I don't understand what the array-entry is doing for us with regard to that
second argument. What am I missing here (either in the way I entered the
formula or in my understanding of what the array-entry method is doing)?

Rick

Rick,

You need to array-enter it into *all* the cells, not just B1.

One way:

1. Select B1:B30
2. Enter formula into function bar (retaining the B1:B30 selection).
3. <ctrl<shift<enter

If you did it correctly, the identical "braced" formula will be in all the
cells, and the results should be as described.
--ron

You need to array-enter it into *all* the cells, not just B1.

One way:

1. Select B1:B30
2. Enter formula into function bar (retaining the B1:B30 selection).
3. <ctrl<shift<enter

If you did it correctly, the identical "braced" formula will be in all the
cells, and the results should be as described.

Thanks... I have not worked with array entered formulas too much yet, so I
missed that "little" piece of the puzzle.

Okay, in order to help my array-entered-formula learning experience, what
benefit does this method have over the one I posted? I mean, each method
requires something to be filled into each cell in B1:B30, and the two fill
processes are roughly equivalent to implement, so what is it about the
array-enter method that Jen finds so attractive?

Rick

Hello Rick,

Some beginner cannot easily change the formula by just entering some
wrong value into B1 or B2 or ...

You have to know that you need to select B1:B30 before you either
delete or array-enter something different formula.

So the array formula is "naively" safer. I actually prefer to unlock
the cells a user may change and then to protect the worksheet to
prevent the user to change any formulas.

Your approach is not slower. In fact FastExcel says that both
approaches are almost equally fast.

Regards,
Bernd

On Sat, 30 Jun 2007 13:45:48 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

You need to array-enter it into *all* the cells, not just B1.

One way:

1. Select B1:B30
2. Enter formula into function bar (retaining the B1:B30 selection).
3. <ctrl<shift<enter

If you did it correctly, the identical "braced" formula will be in all the
cells, and the results should be as described.

Thanks... I have not worked with array entered formulas too much yet, so I
missed that "little" piece of the puzzle.

Okay, in order to help my array-entered-formula learning experience, what
benefit does this method have over the one I posted? I mean, each method
requires something to be filled into each cell in B1:B30, and the two fill
processes are roughly equivalent to implement, so what is it about the
array-enter method that Jen finds so attractive?

Rick


If I recall what Jen posted correctly, try messing things up by, for example,
just deleting the formula in B10. You'll find you can't do it; and that
appealed to him.

What I, personally, find aggravating, is that you can't edit the formula
without selecting the entire array -- but that may be a plus to some.
--ron