I can't explain the results you're seeing w/Offset, but I'm not following why
it is needed. I assume your purpose for using COLUMN is so that you can copy
the formula across. Is there a reason you can't use a relative column
reference in the range address. When you copy one column over, it looks like
you would want the sum of squares for BE590:BE593, right?

=SUMPRODUCT(BD$590:BD$593^2)


" wrote:

G'Day,

I'm trying to use offsets in an array formula to generate a matrix of
values. As usual, I'm taking it slowly to build up the detail, but I'm
getting stumped early-on.

(All entries take place in a cell in column BL, so COLUMN() evalutes to
64.)

=OFFSET($BD$590:$BD$593,0,COLUMN()-64)

This works OK, entered as an array formula.

=OFFSET($BD$590:$BD$593,0,COLUMN()-64), works well although I can only
see the first value in $BD$590:$BD$593 when I Evaluate Formula or use
F9 while editing the cell.

=OFFSET($BD$590:$BD$593,0,COLUMN()-64)*OFFSET($BD$590:$BD$593,0,COLUMN()-64),
provides the value $BD$590*$BD$590 in a single cell, #VALUE in a range
of cells and when I press F9 while editing the cell.

BUT,

=SUM(OFFSET($BD$590:$BD$593,0,COLUMN()-64)*OFFSET($BD$590:$BD$593,0,COLUMN()-64))
causes #VALUE, no matter what I do. This is a scalar product of
vectors. I've tried other ways of achieving this but all seem to
exhibit similar problems.

Also, =SUM(OFFSET($BD$590:$BD$593,0,COLUMN()-64)) works fine.

It seems the product of the offset ranges is starting to show signs of
failing, but summing the product of offset ranges is too much.

Can anyone explain what is happening here, please? How is my
understanding of array formulas and/or Excel functions failing?

TIA,

Clive