CODEWITHSUNDEEP
arraysgoogle-sheets
user7064221
user7064221

Reputation:

Error when correlating two arrays using MMULT

I'm getting the follow error, after trying to correlate two arrays using MMULT():

ERROR: Function MMULT parameter 1 expects number values. But '' is a empty and cannot be coerced to a number.

It turns out that this only occurs when there are blank cells in the range. How do I make the following part of the formula ignore blank cells:
ARRAYFORMULA(MMULT('E1:Sample'!$D$2:$K,TRANSPOSE(SIGN(COLUMN(('E1:Sample'!$D$2:$K))))

Many thanks!

Upvotes: 1

Views: 861

Answers (1)

JPV
JPV

Reputation: 27282

See if this works:

=ARRAYFORMULA(MMULT(N('E1:Sample'!$D$2:$K),TRANSPOSE(SIGN(COLUMN(('E1:Sample'!$D$2:$K))))

The N-function will 'convert' empty cells to 0, while maintaining the values found in the other cells.

Upvotes: 1

Related Questions