Sumproduct and offset

[Guest]

Dear list,

I've got a table that looks like this (numbers on left side are row numbers):
1 S1 S2 S3 S4 S5
2 X1 1 0 1 1 1
3 X2 0 0 0 1 1
4 X3 0 1 0 0 0
5 X4 1 1 0 0 1
For each cell in the next table (symmetric below and above diagonal) I want
the sumproduct of two rows Xi and Xj. Cell X2-X1 should contain the
sumproduct of row X1 and X2.

6
7 X1 X2 X3 X4
8 X1
9 X2 2
10 X3 0 0
11 X4 2 1 1

I tried to solve this by using the following formula:
= SUMPRODUCT(OFFSET($B$2:$F$2;COLUMN()-2;0;1;6);
OFFSET($B$2:$F$2;ROW()-8;0;1;6))
However, this results in zeros in each cell. The help file mentions that
SUMPRODUCT considers non-numerical values as zeros.
Can someone help me with this?

Maarten

 

[Bob Phillips]

Maarten,

Use this formula in B8

=IF($A8>B$7,SUMPRODUCT(OFFSET($A$1,MATCH($A8,$A$2:$A$5,0),1,1,5),OFFSET($A$1
,MATCH(B$7,$A$2:$A$5,0),1,1,5)),"")

and copy down and across.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Maarten wrote...
....

I tried to solve this by using the following formula:
=SUMPRODUCT(OFFSET($B$2:$F$2;COLUMN()-2;0;1;6);
OFFSET($B$2:$F$2;ROW()-8;0;1;6))
However, this results in zeros in each cell. The help file mentions that
SUMPRODUCT considers non-numerical values as zeros.

....

The help file doesn't bother to mention that COLUMN() and ROW() called
exactly so both return single-entry arrays. It also doesn't mention
that calling OFFSET with array 2nd or 3rd arguments produces a result
with an undocumented data type which behaves in some ways like an array
of range references. Such results can only be used as arguments to
functions that expect range arguments.

You need to convert the COLUMN() and ROW() calls to scalars. The
shortest way to do so is

=SUMPRODUCT(OFFSET($B$2:$F$2;SUM(COLUMN())-2;0;1;6);
OFFSET($B$2:$F$2;SUM(ROW())-8;0;1;6))

 

[Herbert Seidenberg]

Here is a version in the R1C1 style
=SUMPRODUCT(INDIRECT(rowh R,0),INDIRECT(colh C,0))
rowh and colh are the named ranges of the second array Xi headers.
Name the X1 thru X4 ranges of the first array as such.

 

[bplumhoff]

Hi Maarten,

If your table resides in cells B2:F5 (values, not row or column
titles!), then select cells B7:E10 and array enter

=MMULT(B2:F5,TRANSPOSE(B2:F5))

(enter with CTRL+SHIFT+ENTER).

Please notice that no empty cell is allowed in B2:F5 (see help on
MMULT).

HTH,
Bernd