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

"JoCa" wrote:

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

"Lori" wrote:

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