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


"Shane Devenshire" wrote:

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
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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