Forum Discussion
INDIRECT function not returning array
Hi everyone,
Thank you in advance for your time and your help.
I am using the AVERAGE and FORECAST.ETS functions on account balances to forecast them in Excel. Both of these functions require that I enter different ranges as arguments (e.g., the range of values to average for AVERAGE, and ranges for the "values" and "timeline" arguments of FORECAST.ETS).
The addresses of the ranges I am using will change with the number of periods I am averaging or using to forecast, so I am using the INDIRECT function to piece together the correct range addresses and get the arrays of values for use in the functions.
The problem I am having is that, while the INDIRECT functions I am using are referencing the correct ranges (and the cells in these ranges all contain numeric values), the INDIRECT functions are only returning the first cell's value within each range. Both the AVERAGE and FORECAST.ETS functions are returning #VALUE!
Here's what I'm working with:
When I highlight this portion of my AVERAGE function...
...the ToolTip says {"E133:J133"}, which is the range I want to reference, shown here:
But when I highlight the entire INDIRECT function, the ToolTip shows just the first value of the range {1578.95} as opposed to something like {1578.95,5365.32,13089.65,921.63,-324.24,-4215.14}
And I am having the same problem with INDIRECT ranges in the FORECAST.ETS function.
My first guess at what the problem is is that it has something to do with the rounded brackets ("{}") that appear in the ToolTips. I tried doing the same AVERAGE function above in a blank workbook and with a simpler address for INDIRECT to reference. When I highlight this portion of the new function...
...the ToolTip returns "E133:J133", which is different from when I highlighted my first function in that there are no rounded brackets enclosing the address. I think this is the problem but it is just an idea.
Any guidance would be greatly appreciated. Thanks again.
2 Replies
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
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
