Array results and performance

[Dave Bruns]

Many formulas return an array result when given an array. For example, =ROW(range) returns an array of row numbers.

And this formula returns an array of cell addresses in a range:

=ADDRESS(ROW(range),COLUMN(range))

But when entered as a single cell formula, you may not realize you're getting back an array, since Excel happily displays just the first value.

My question: from a performance standpoint, does this matter?

In other words, is Excel smart enough to calculate only the first item in an array result when only one item is needed?

Or does Excel calculate all array results regardless?

 

[DRSteele]

Excel calculates all the array results.

 

[Dave Bruns]

Excel calculates all the array results.

It's interesting, though. If I define a very large named range called "data" with 10 million cells (1,000,000 rows x 10 columns), I don't notice any real delay if I use formulas like:

=ADDRESS(ROW(range),COLUMN(range))

Even though the "resultant array" is extremely large.

But if I ask for random item in the array with INDEX:

=INDEX(ADDRESS(ROW(data),COLUMN(data)),1000)

There is a significant delay before Excel returns the result. It could be that INDEX is to blame as it churns through the array, but it does seem that Excel is highly optimized for situations where a resultant array is being displayed in a single cell (i.e. only the first item in the array is displayed).

 

[Dave Bruns]

Whoops, that first formula should have been:

=ADDRESS(ROW(data),COLUMN(data))

Where "data" is the giant named range.