Indirect reference causes statistical function to throw up a #NUM!

[JoCa]

Folks,

I ran into an odd behavior when trying to use an indirect reference to a
range used as an argument in a statistical function.

A:
1: Data
2: 1
3: 2
4: 3
5: 4
6: 5

B2: =ADDRESS(ROW(),COLUMN($A$1)) produces “$A$2â€

C2:
=FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFSET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far.

D2:
=FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))),1,0,4,1))
produces an error #NUM! This is unexpected, since the formula in C2 works
fine.

Interestingly enough, if you use the same INDIRECT references in cell D2 as
arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works
fine. Try putting the formula below on E2. However, functions like CORREL(),
COVAR(), etc. do not work.

E2:
=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))),1,0,4,1)) produces a value of 28 as expected

Is this odd or what? Any body knows why using the
OFFSET(INDIRECT(ADDRESS())…) combination does not work for some statistical
functions?

Hopefully we can all learn something new.

Thanks in advance

 

[Luke M]

The problem is not with the statistical function, its an improper use of
INDIRECT. INDIRECT gathers info to form a cell reference, and then goes to
that cell and pulls the value. Thus, when INDIRECT looks at B2, it sees $A$2,
and sends this to the OFFSET function. Your latter formula however, INDIRECT
sees $A$2, goes there, finds 1, and sends this to OFFSET. This makes no sense
to the OFFSET function, causing your error.

However, the way you have your formula currently written, it could be
simplified to:
=FORECAST($A$2,OFFSET($A$1,1,0,4,1),OFFSET($A$1,1,0,4,1))
Since your ADDRESS function was set to return absolute references. Might I
ask why you were going through the effort of using ADDRESS/INDIRECT?

 

[JoCa]

Luke,

Thanks for your help. The reason I am using the ADDRESS/INDIRECT is that I
am combining a series of heuristic models to analyze data where new fields
and/or records can be added to the dataset in add/insert mode and the models
have to find the optimal amount of data to calculate some parameters. The
example I submitted was just a something I made up with the minimal
properties necessary to demonstrate the problem.

I am using the column headers as references to make my formulas able to
auto-update when new data is incorporated, and sometimes the initial
reference to the target range is an interception and that is why the
ADDRESS(ROW(),COLUMN()) is so important to me.

So, the question is then: If I know the row# and column# of my initial
reference cell, how can I combine it into a reference that would make sense
to the OFFSET function as a single-cell range (not a value) for the FORECAST
calculation???

 

[Shane Devenshire]

Hi,

You really should tell us what you are trying to do - your formula serves no
purpose since you are assuming that the know X's and know Y's are identical -
in which case the forecast is always 1 and you don't need a function at all.

Second, if you fix A2 and B2 as absolute you are apparently not going to
copy the formula, so in that case there is no need for absolute cell
references. Since the forecast is for the value in A2 which is fixed, and
since the result will always be 1 you can simply write =A2.

You might want a formula like the following where column B is different than
column A and you want a rolling forecast, but of course this is only a guess:

=FORECAST(A2,OFFSET(A2,1,,4),OFFSET(B2,1,,4))

if you were

 

[JoCa]

Shane, Thanks for your help. I should have known in advance that the
super-simplification of my example was going to cause confusion.

Like I said in my reply to Luke’s post, the example I submitted was just a
something I made up with the minimal properties necessary to demonstrate the
problem. Of course, if the know X's and know Y's are identical the slope is 1
and the intercept is 0, so the forecast is always the X value. It was done on
purpose to minimize effort needed to understand the problem I am having.

Please, take my example (including the absolute references) in context. It
was only meant to demonstrate an apparent inconsistency I thought existed.
Luke explained how my usage of the INDIRECT function was wrong. Explaining
what I am doing, even if I reduce it to a minimum would be tedious for anyone
to read.

So, the question is then: If I know the row# and column# of an initial
reference cell, how can I combine it into a complete reference that would
make sense to the OFFSET function as a single-cell range (not a value) for
the FORECAST calculation???

 

[Luke M]

Hmm. I believe I must apologize. In further analysis there does appear to be
something odd going on. In simplifying your formula down a bit:

=FORECAST($A$2,OFFSET(INDIRECT("A"&2),1,0,4,1),OFFSET(A2,1,0,4,1))
vs.
=FORECAST($A$2,OFFSET(INDIRECT("A"&ROW(A2)),1,0,4,1),OFFSET(A2,1,0,4,1))

These two formula "should" produce the exact same result. They contain same
functions, layout, etc. However, the one with the ROW function returns an
error, while the first does not. In further analysis, it appears that the
functions within OFFSET are returning arrays, instead of single values. We
can compensate by forcing a SUM (of what should be a single value)

=FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(SUM(ROW()),SUM(COLUMN($A$1)))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(SUM(ROW()),SUM(COLUMN($A$1)))),1,0,4,1))

So, it appears that you can use your method of consturcting an ADDRESS, you
will just need to use the SUM method to force a single-value. A potential
problem now may be the nested function limit (currently at 6, limit is 7) but
that's a whole other issue.

 

[Luke M]

Further detail:
Because the FORECAST function is looking for an array of numbers, it's
causing any function within that arguement to return an array. While this
could be helpful, as OFFSET is not setup to deal with arrays, even
single-digit ones, it causes the crash.

Statistical functions tend to deal with arrays more often than traditional
functions, so this is why the problem seems more prevalent in them.

 

[Lori]

This is quite subtle behavior due to ROW/COLUMN returning (single element)
arrays.
You could try SUM() around each one to make them ordinary values.

Normally if you enter OFFSET/INDIRECT in a formula, the reference is changed
to a value automatically. But if arrays are included in the arguments, Excel
needs the help of another function such as N() or T() to do the conversion.
This can be useful in some situations eg for doing operations on more than
one sheet.

For a deeper understanding you need to delve into how values are represented
internally, see http://msdn.microsoft.com/en-us/library/bb687869.aspx
(i believe an R type pointer gets passed to a function that accepts
xltypeRef arguments.)

I think it was MVP Laurent Longre who discovered this.

 

[JoCa]

Lori, Thanks for your contribution. Identifying this problem properly was
very helpful. I like your suggestion and I really appreciate it!

 

[JoCa]

Luke, Thank you so much for your help. You are a true pro for digging deeper
into the problem. Thanks for your suggestion. I appreciate it!

 

[Lori]

I now see Luke got there first on the sum fix, glad to be of help anyway.