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!

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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),OFF SET(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(ADDRE SS(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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"JoCa" wrote:

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???


"Luke M" wrote:

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?

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"JoCa" wrote:

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),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far.

D2:
=FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($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