ExcelBanter - Editing Array Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Editing Array Formula (https://www.excelbanter.com/excel-worksheet-functions/41850-editing-array-formula.html)

[email protected]

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

Harlan Grove

wrote...
....
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?

Simplifying the formula to =ROW(INDIRECT("1:8")), if you want this to
be variable in the number of rows, and the most rows you'd ever want
were 10, then you could use the defined name N to refer to the size of
the result, and you could enter the following array formula into a
10-row by 1 column range.

=IF(ROW(INDIRECT("1:10"))<=N,ROW(INDIRECT("1:"&N)) ,"")

For your particular formula, it'd be better to use nonarray formulas
like

X1:
=MAX(DataRange)

X2:
=IF(ROWS(X$1:X2)<=N,LARGE(DataRange,ROWS(X$1:X2)), "")

Select X2 and fill down as far as needed, e.g., into X3:X10.

MrShorty

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

[email protected]

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

Tushar Mehta

(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

RagDyeR

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

Harlan Grove

"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

Harlan Grove

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

All times are GMT +1. The time now is 10:30 PM.