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