INDIRECT function not returning array

jmw_87 

In legacy (i.e. pre-Office 365) versions of Excel, the ROW function will be coerced into returning an array in such constructions, albeit an array comprising a single value only.

Taking the following formula, for example, and assuming it lies somewhere in row 1,

=AVERAGE(INDIRECT(ADDRESS(ROW(),5,4)&":"&ADDRESS(ROW(),7,4)))

In legacy versions of Excel this will resolve to

=AVERAGE(INDIRECT(ADDRESS({1},5,4)&":"&ADDRESS({1},7,4)))

which, importantly, is not equivalent to

=AVERAGE(INDIRECT(ADDRESS(1,5,4)&":"&ADDRESS(1,7,4)))

since the former is passing an array as ADDRESS's row_num parameter, and so resolves further to

=AVERAGE(INDIRECT({"E1"}&":"&{"G1"}))

i.e.

=AVERAGE(INDIRECT({"E1:G1"}))

and, again, we have a crucial difference between this and

=AVERAGE(INDIRECT("E1:G1"))

since, unless properly coerced, when passed a range in array form INDIRECT will parse the first element of that range only.

You can resolve this by coercing the return from ROW to a non-array, e.g. using MIN or SUM:

=AVERAGE(INDIRECT(ADDRESS(MIN(ROW()),5,4)&":"&ADDRESS(MIN(ROW()),7,4)))

though to be honest there are much better and simpler constructions available which avoid such issues and which do not employ the volatile INDIRECT.

Regards