Editing Array Formula

[[email protected]]

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]

[email protected] 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.

[[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 <[email protected]>,
[email protected] 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 !
----------------------------------------------------------------------------
-------------------

<[email protected]> wrote in message
news:%[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

[Harlan Grove]

"Tushar Mehta" <[email protected]> wrote in message
news:[email protected]...
> (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 <[email protected]>,
> [email protected] 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" <[email protected]> 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].