cell reference change

**freaksugar** · 03-21-2010, 07:32 PM

here is the question I have, any help and comments are much appreciated:
sheet 1 has a 50000x12 matrix(MA) and a 12x1 matrix (MB), I want to get a new matrix of 1000x50 to multiply them by having first 50 rows in each column from MA to multiple MB.
therefore, in the sheet 2 column A, the formula in each cell is :
A1: sumproduct(MA(A1:A50),MB($A$1:$A$12))
A2: sumproduct(MA(A51:A100),MB($A$1:$A$12))
A3: sumproduct(MA(A101:A150),MB($A$1:$A$12))
......
A1000: sumproduct(MA(A49950:A50000),MB($A$1:$A$12))
......
B1: sumproduct(MA(B1:B50),MB($A$1:$A$12))
B2: sumproduct(MA(B51:B100),MB($A$1:$A$12))
B3: sumproduct(MA(B101:B150),MB($A$1:$A$12))
.....
B1000: sumproduct(MA(B49950:B50000),MB($A$1:$A$12))

.....
until column L is filled.

is there an easy to write the formula? pls help

**teylyn** · 03-21-2010, 08:12 PM

Hi freaksugar, welcome to the forum. Try

A1: sumproduct((INDEX($A:$A,(ROW()-1)*50+1):INDEX($A:$A,ROW()*50))*($A$1:$A$12))

copy down. Along the same vein

B1: sumproduct((INDEX($B:$B,(ROW()-1)*50+1):INDEX($B:$B,ROW()*50))*($A$1:$A$12))

copy down

**freaksugar** · 03-21-2010, 09:35 PM

Thank you!!!

Can i ask you another question to extend this further?

For example, if I would like to sumproduct 2 matrices as following:

in the sheet 2 column A, the formula in each cell is :
A1: sumproduct(MA(A3:O3),MB($A$1:$A$12))
A2: sumproduct(MA(A53:O53),MB($A$1:$A$12))
A3: sumproduct(MA(A103:O103),MB($A$1:$A$12))
......
A1000: sumproduct(MA(A50003:O50003),MB($A$1:$A$12))
......
B1: sumproduct(MA(A4:O4),MB($A$1:$A$12))
B2: sumproduct(MA(A54:O54),MB($A$1:$A$12))
B3: sumproduct(MA(A104:O104),MB($A$1:$A$12))
.....
B1000: sumproduct(MA(A50004:O50004),MB($A$1:$A$12))

should i write this?

A1: sumproduct((INDEX($A:$O,(ROW(3)-1)*50+1):INDEX($A:$O,(ROW(3)-1)*50+1))*($A$1:$A$12))

then

B1: sumproduct((INDEX($B:$O,(ROW(3)-1)*50+1):INDEX($B:$O,(ROW(3)-1)*50+1))*($A$1:$A$12))
?

Thanks again if you could look at it....