On Tue, 27 Apr 2004 14:01:50 -0500, JWolf wrote:

Ron:
Really great formula, but I have two questions:
The portion of the formula: {=row(indirect("1:5"))} returns an array
{1;2;3;4;5}
The portion of the formula: {=indirect("1:5")} returns an array too
large to display.

1) What is the purpose of this formulation, i.e. row(indirect("1:5")?

In the event either factor needs to be modified, it can be simple, with this
formulation, to perform a text concatenation. For example:

=row(indirect("1:"& min(count(score),5))) would return an array
1;2;...n where n is the lesser of the number of scores in count, or five.



I only ask this because the array {1;2;3;4;5} can be returned with
{=row(a1:a5)} directly and would seem to take less overhead. My concern
with overhead is that array formulas seem to randomly crash excel,
especially as the quantity of array formulas in a workbook increase.

If there is no need for modifications, even simpler would be to just use the
array constant {1,2,3,4,5}.

So:

=SUM(TRANSPOSE(INDIRECT(ADDRESS(
LARGE(ISNUMBER(score)*ROW(score),
{1,2,3,4,5}),COLUMN(score)))))

I have not had a problem with array formulas randomly crashing my Excel
installation.

2) What is in the array created with {indirect("1:5")}?

An array formed by performing the INDIRECT function on every cell in the range
A1:IV5.


--ron