Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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 -

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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
might have existed and I just didn't know it at the time<g).

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

Showing Top 30 with array-formula?
 

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

Showing Top 30 with array-formula?
 

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


"Ron Rosenfeld" wrote in message
...
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

Showing Top 30 with array-formula?
 

On Sat, 30 Jun 2007 14:00:31 -0700, "RagDyeR" wrote:

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

Showing Top 30 with array-formula?
 

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 !
---------------------------------------------------------------------------
"Ron Rosenfeld" wrote in message
...
On Sat, 30 Jun 2007 14:00:31 -0700, "RagDyeR"
wrote:

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

Showing Top 30 with array-formula?
 

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

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




Ragdyer wrote:

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 !
---------------------------------------------------------------------------
"Ron Rosenfeld" wrote in message
...
On Sat, 30 Jun 2007 14:00:31 -0700, "RagDyeR"
wrote:

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

--

Dave Peterson

Showing Top 30 with array-formula?
 

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 !
---------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
Ah, but it would be nice if he remembered that stuff!

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




Ragdyer wrote:

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
!

--------------------------------------------------------------------------
-
"Ron Rosenfeld" wrote in message
...
On Sat, 30 Jun 2007 14:00:31 -0700, "RagDyeR"
wrote:

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

--

Dave Peterson

Showing Top 30 with array-formula?
 

Thanks <vbg.

Ragdyer wrote:

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 !
---------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
Ah, but it would be nice if he remembered that stuff!

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




Ragdyer wrote:

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
!

--------------------------------------------------------------------------
-
"Ron Rosenfeld" wrote in message
...
On Sat, 30 Jun 2007 14:00:31 -0700, "RagDyeR"
wrote:

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

--

Dave Peterson

--

Dave Peterson