Thanks in advance for any hints, pointers and/or suggestions. This is driving me crazy.
I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.
For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11.
I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)
=SUMPRODUCT(OFFSET($A$1,1,COLUMN(B2)-COLUMN($A$1),10,1),OFFSET($A$1,ROW(B2)-ROW($A$1),10,1))
But I keep getting a #VALUE! error.
What really drives me crazy is that if I do something like:
=SUMSQ(OFFSET($A$1,1,COLUMN(B2)-COLUMN($A$1),10,1))
, that works perfectly fine (only for the diagonals) but I'm still at a loss for off-diagonal elements.
Any help will be greatly appreciated. Thanks!
Bookmarks