Showing Top 30 with array-formula?

[Jen.Carllson]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Mike H]

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

 

[Mike H]

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

 

[Bernd P]

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

 

[Jen.Carllson]

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

 

[Jen.Carllson]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bernd P]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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.

That would be my approach as well. You have to remember, I am newly returned
to Excel after a long, long absence; I don't remember array-entered formulas
when I last played with Excel back in the early to mid-1990s (of course, it

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

Whew! Glad to hear that.

Thanks for answering my question... much appreciated.

Rick

 

[Rick Rothstein \(MVP - VB\)]

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.

Yeah, I see that now (I just read Bernd's reply and he mentioned that as
well).

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.

That would probably drive me nuts as well.

Thanks for answering my question... I appreciate it.

Rick

 

[RagDyeR]

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

NOT really accurate!

Actually, it's *easier* to revise this type of array *entered* formula.

Select *any one* of the cells containing this type of formula.
Make your revision, and then simply CSE ... and *every* formula within the
*entire* array is revised.

To delete the array, select *any one* of the cells in the array, hit <Ctrl>
< / >, then <Delete>.

The supposed advantage of this type of formula is the conservation of XL's
resources, since XL is only storing a single formula.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

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

 

[Ron Rosenfeld]

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

NOT really accurate!

Actually, it's *easier* to revise this type of array *entered* formula.

Select *any one* of the cells containing this type of formula.
Make your revision, and then simply CSE ... and *every* formula within the
*entire* array is revised.

To delete the array, select *any one* of the cells in the array, hit <Ctrl>
< / >, then <Delete>.

The supposed advantage of this type of formula is the conservation of XL's
resources, since XL is only storing a single formula.

Thanks for those pointers. In particular, I was unaware of the <ctrl></>
--ron

 

[Ragdyer]

Learned that one from Dave Peterson a long time ago, in a thread where we
were discussing this very same subject..
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

 

[Dave Peterson]

Ah, but it would be nice if he remembered that stuff!

He still uses Edit|Goto|special|current array.

Learned that one from Dave Peterson a long time ago, in a thread where we
were discussing this very same subject..

 

[Ragdyer]

3 years isn't really that long ago.<g>

As a reminder:

http://tinyurl.com/3btqcp


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Ah, but it would be nice if he remembered that stuff!

He still uses Edit|Goto|special|current array.

Learned that one from Dave Peterson a long time ago, in a thread where we
were discussing this very same subject..
--
Regards,

RD

--------------------------------------------------------------------------

-

Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-