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

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

Thanks for the reply.

My example was poorly chosen since it does look as though I'm trying to
calculate the sum of squares.

I'm really trying to check that a set of eigenvectors are orthonormal.
I'm aiming to calculate

=SUMPRODUCT(X(i),X(j)) (via =SUM(X(i)*X(j)) )

where i,j = 1, ..., 31, and X(i) is a 31x1 column vector of real
values. The calculations are to be placed in a 31x31 matrix which
should be the identity matrix.

I have the eigenvectors in adjacent columns, so to pick them out as I
create the matrix, I'm using X(1) plus a column offset relative to the
first eigenvector.

Hope this helps to explain the situation. I'm certainly not saying it
is the most efficient approach.

Thanks again,

Clive

Afraid you're over my head -I'm an accountant/auditor. Maybe some actual
numbers would help. This is a bit of a guess based on trying to interpret
your original formula, so please provide a correct example if none of it
helps you.

If, in A1:A3, you have

1
2
8

are you trying to get:

11 (SUM(1*1, 1*2, 1*8))
22 (SUM(2*1, 2*2, 2*8))
88 (SUM(8*1, 8*2, 8*8))

which would be =SUM(A$1:A$3)*A1 (enter it in a single cell, then copy the
formula down and across - the column references are relative)

or are you trying to find 121 (11+22+88) which, as a non-array formula, is
=SUMPRODUCT(SUM(A$1:A$3)*A$1:A$3)



" wrote:

Thanks for the reply.

My example was poorly chosen since it does look as though I'm trying to
calculate the sum of squares.

I'm really trying to check that a set of eigenvectors are orthonormal.
I'm aiming to calculate

=SUMPRODUCT(X(i),X(j)) (via =SUM(X(i)*X(j)) )

where i,j = 1, ..., 31, and X(i) is a 31x1 column vector of real
values. The calculations are to be placed in a 31x31 matrix which
should be the identity matrix.

I have the eigenvectors in adjacent columns, so to pick them out as I
create the matrix, I'm using X(1) plus a column offset relative to the
first eigenvector.

Hope this helps to explain the situation. I'm certainly not saying it
is the most efficient approach.

Thanks again,

Clive

OK, let's try this.

A1:C3 has the data

A B C
1 4 3 6
2 5 2 5
3 6 1 4

What I'm trying to achieve is another nine values in a sqaure array:

4*4+5*5+6*6 4*3+5*2+6*1 4*6+5*5+6*4

3*4+2*5+1*6 3*3+2*2+1*1 3*6+2*5+1*4

6*4+5*5+4*6 6*3+5*2+4*1 6*6+5*5+4*4


Notice the first number in each product comes from i-th column of the
original square matrix, where i denotes the row in the new matrix.

The second number in each product comes from the j-th column of the
original sqaure matrix, where j denotes the column of the new matrix.

Rows and columns are numbered from 1 to 3 (in this simple case).

The end matrix is symmetrical. The element from row 2, column 1 is the
same as the element from row 1, column 2 since they are both equivalent
to the SUMPRODUCT of the frist and second columns of the original
matrix.

Does this help?

Clive

I think I can help you (finally).

Assume the range containing your data is named Test (or replace w/$A$1:$C$3
or the appropriate range reference for your data). Try entering this formula
in cell E1 (change E1 in the formula to whatever cell you actually enter the
formula in). Then copy across and down.

=SUMPRODUCT(INDEX(Test, ,ROW()-ROW($E$1)+1)*INDEX(Test,
,COLUMN()-COLUMN($E$1)+1))

Maybe the formula could be done w/Offset, but the index function can also
return entire rows and columns. Note the space where the row argument should
be for the Index function.

" wrote:

OK, let's try this.

A1:C3 has the data

A B C
1 4 3 6
2 5 2 5
3 6 1 4

What I'm trying to achieve is another nine values in a sqaure array:

4*4+5*5+6*6 4*3+5*2+6*1 4*6+5*5+6*4

3*4+2*5+1*6 3*3+2*2+1*1 3*6+2*5+1*4

6*4+5*5+4*6 6*3+5*2+4*1 6*6+5*5+4*4


Notice the first number in each product comes from i-th column of the
original square matrix, where i denotes the row in the new matrix.

The second number in each product comes from the j-th column of the
original sqaure matrix, where j denotes the column of the new matrix.

Rows and columns are numbered from 1 to 3 (in this simple case).

The end matrix is symmetrical. The element from row 2, column 1 is the
same as the element from row 1, column 2 since they are both equivalent
to the SUMPRODUCT of the frist and second columns of the original
matrix.

Does this help?

Clive

JMB,

Many thanks. It works, as of course you knew!

I'm still curious as to why my original approach suudenly doesn't work.

Building it up is fine up to a point and then #value.

Must be something deeper.

Apologies for getting back to you so slowly.

Cheers,

Clive

Quite alright - thanks for posting back to confirm that it worked for you - I
was wondering about it. I admit I'm also little curious as to why Offset
behaved that way. I would have thought it would have given some result
(maybe not the one you wanted - but something other than an error).



" wrote:

JMB,

Many thanks. It works, as of course you knew!

I'm still curious as to why my original approach suudenly doesn't work.

Building it up is fine up to a point and then #value.

Must be something deeper.

Apologies for getting back to you so slowly.

Cheers,

Clive