J

#### JoCa

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