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