CODEWITHSUNDEEP
google-sheets
Comfort Eagle
Comfort Eagle

Reputation: 2462

Google Spreadsheets: CORREL() and MMULT() with missing cases/blanks

So I've got the following formula to correlate two ranges:

=ROUND(CORREL(ARRAYFORMULA(MMULT('E0:Sample'!$D$2:$AY,TRANSPOSE(SIGN(COLUMN(('E0:Sample'!$D$2:$AY)))))),FILTER(OFFSET('E0:Sample'!$D$2:$D,0,ROW()-2),NOT(ISBLANK(OFFSET('E0:Sample'!$D$2:$D,0,ROW()-2))))),3)

The formula works fine, as long as there are no blanks in 'E0:Sample'!$D$2:$AY. Otherwise the error message Function MMULT parameter 1 expects number values. But '' is a empty and cannot be coerced to a number. is thrown.

I´ve tried to filter() for empty rows, but the filter-function won't work since the ranges differ.

How do I solve this without the best way?

Thanks!

Upvotes: 1

Views: 959

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34265

It's difficult to test your complete formula, but I did a test on a mini-version of matrix multiply and it seems that you can use the N function the same way as you can in Excel. Here is my mini-test:-

=ArrayFormula(MMULT(n(B1:G1),n(A1:A6)))

where both ranges contain a mix of numbers, alphas and blanks. Non-numeric cells are treated as zeroes.

Reference

I'm not totally clear about the context for this - I think you're trying to get the row sums from your large 2D array by using the mmult - if this is correct I think my answer is OK because the blanks would contribute nothing to the sums. Since CORREL ignores blanks in the second range, you don't need to filter at all?

I did eventually set up some test data for your formula, and my formula ended up like this:-

=ROUND(CORREL(ARRAYFORMULA(MMULT(n('E0:Sample'!$D$2:$AY),TRANSPOSE(SIGN(COLUMN(('E0:Sample'!$D$2:$AY)))))),OFFSET('E0:Sample'!$D$2:$D,0,ROW()-2)),3)

Upvotes: 1

Related Questions