I have a correlation matrix (say matrix A) in Excel with about 4,200 rows and the same number of columns, which is quite large for the everyday Excel user.

I also have another matrix (B) with the same number of rows and 1 column. For each row i of the matrix A, I want to multiply its element Aij by the elements Bi1 and Bj1, and have the sum of the elements in each row of A after that multiplication.

That sum of rows is what is relevant, the matrices are not. That could be easily achieved with formulas, but with that amount of data the file becomes too large to be manipulated.

Are there any better ways of going about this in Excel?

CodePudding user response：

I would not rule out using a formula. I have a Ryzen 7 2700x on 16G of ram, with Excel O365. I ran a few methods on a 4200 x 4200 floating point sample and got surprising runtimes. I tried a few different approaches. The fastest was this. I think this gets you the result you wanted (requires O365):

=LET(
matrix,MMULT(SmallTable,TRANSPOSE(SmallTable))*BigTable,
BYROW(matrix,LAMBDA(r,SUM(r)))
)

I named the 4,200 x 1 array SmallTable and the 4,200 x 4,200 array BigTable.

And I get < 3 second completion times.