Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default Editing Array Formula

I have just recently started to use Array formula that return multiple
results.
eg, select a range of cells 1 column x 8 rows and enter
=LARGE(DataRange,ROW(INDIRECT("1:8"))) array entered
to give a list of the top 8 results

My question relates to the difficulty I seem to have editing it to say the
top 6
I can easily reselect the range and change the formula. Then get errors
for the last two cells.
If I try to remove the now redundant cells I get a "You cannot change part
of an Array" error

In fact the only way I have to make all well seems to be delete and start
again.
If this the only way?

Please help
Cheers RES
  #3   Report Post  
MrShorty
 
Posts: n/a
Default


My approach:

1) Select one cell of array formula, copy, and paste in a nearby cell
(copy only one cell)
2) Delete old array
3) copy single cell back to original position of first cell.
4) select desired number of cells (active cell on cell with formula in
it)
5) Push F2, then ctrl-shft-enter

If there's a better way, I'm interested, because I have to do this,
too.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=398180

  #4   Report Post  
 
Posts: n/a
Default

Thanks to both Harlan and MrShorty for responding

My post was mostly about the general subject of editing an array formula
that return results into a range of cells.
It sounds like the way I am doing it is pretty much the same as MrShorty.

Harlan that is a nice trick to mask the fact the return range is covering
more cells than are needed for the array.
Still not sure I fully have my head round it but will give it a go.

many thanks RES
  #5   Report Post  
Tushar Mehta
 
Posts: n/a
Default

(1) Change the array formula to a non-array formula:
Select the range containing the formula. At this point the formula bar
should show the formula with curly brackets around it. Click in the
formula bar. Don't do any editing, just press CTRL+ENTER (*no* SHIFT).
The removes the array formula and simply duplicates the formula in
every cell (the formula in the formula bar should no longer have curly
brackets around it). If you see all kinds of errors, ignore them.

(2) Make the non-array formula into an array formula for the new
smaller range:
Select the new abbreviated range. Click the formula in the formula
bar. Press CTRL+SHIFT+ENTER.

(3) Delete (or do whatever else) with the cells no longer part of the
array.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have just recently started to use Array formula that return multiple
results.
eg, select a range of cells 1 column x 8 rows and enter
=LARGE(DataRange,ROW(INDIRECT("1:8"))) array entered
to give a list of the top 8 results

My question relates to the difficulty I seem to have editing it to say the
top 6
I can easily reselect the range and change the formula. Then get errors
for the last two cells.
If I try to remove the now redundant cells I get a "You cannot change part
of an Array" error

In fact the only way I have to make all well seems to be delete and start
again.
If this the only way?

Please help
Cheers RES



  #6   Report Post  
RagDyeR
 
Posts: n/a
Default

What am I missing?

Why not simply take the "array" *out of* the array formula?

For your particular situation, enter this formula in say B1:

=LARGE($A$1:$A$20,ROW(1:1))

*NOW*, simply drag down to copy as many rows as you anticipate that you
might need.

The formula in each cell is a separate entity, which can be revised and/or
deleted as you wish, but *still* maintains it's functionality to return an
array of values.

What you lose with this type of construction is the ability of the formula
to *adjust itself*, or *protect itself*, as far as adding or deleting
rows/columns.

If you don't anticipate changes to the sheet, or even if you do, but you're
aware of this situation, you can more easily utilize formulas in this form.

--

Regards,

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

wrote in message
...
Thanks to both Harlan and MrShorty for responding

My post was mostly about the general subject of editing an array formula
that return results into a range of cells.
It sounds like the way I am doing it is pretty much the same as MrShorty.

Harlan that is a nice trick to mask the fact the return range is covering
more cells than are needed for the array.
Still not sure I fully have my head round it but will give it a go.

many thanks RES


  #7   Report Post  
Harlan Grove
 
Posts: n/a
Default


"Tushar Mehta" wrote in message
om...
(1) Change the array formula to a non-array formula:
Select the range containing the formula. At this point the formula bar
should show the formula with curly brackets around it. Click in the
formula bar. Don't do any editing, just press CTRL+ENTER (*no* SHIFT).
The removes the array formula and simply duplicates the formula in
every cell (the formula in the formula bar should no longer have curly
brackets around it). If you see all kinds of errors, ignore them.

(2) Make the non-array formula into an array formula for the new
smaller range:
Select the new abbreviated range. Click the formula in the formula
bar. Press CTRL+SHIFT+ENTER.

(3) Delete (or do whatever else) with the cells no longer part of the
array.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have just recently started to use Array formula that return multiple
results.
eg, select a range of cells 1 column x 8 rows and enter
=LARGE(DataRange,ROW(INDIRECT("1:8"))) array entered
to give a list of the top 8 results

My question relates to the difficulty I seem to have editing it to say
the
top 6
I can easily reselect the range and change the formula. Then get errors
for the last two cells.
If I try to remove the now redundant cells I get a "You cannot change
part
of an Array" error

In fact the only way I have to make all well seems to be delete and start
again.
If this the only way?

Please help
Cheers RES



  #8   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Tushar Mehta" wrote...
(1) Change the array formula to a non-array formula:
Select the range containing the formula. . . .


Actually it'd be easier to press [F2] immediately followed by
[Ctrl]+[Shift]+[Enter]. That'll automatically select the entire array range.
Then press [F2] immediately followed by [Ctrl]+[Enter].


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can you use count with an array formula similar to using sum PhilH Excel Worksheet Functions 3 June 27th 05 08:00 AM
Trouble shooting#NA error in Array formula RonR Excel Discussion (Misc queries) 2 June 14th 05 09:58 PM
How can I use a cell reference in Sumproduct array formula? Chrism Excel Discussion (Misc queries) 1 April 7th 05 05:05 PM
Array formula reference (Corrected) JAK Excel Discussion (Misc queries) 3 February 22nd 05 04:38 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 06:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"