Considering blank cells and text as zeros in MMULT

[someguywantinghelp]

I have a LibreOffice calc spreadsheet, which has the following operation: MMULT(C5:F5, TRANSPOSE(C7:F7)). The operation works as expected, unless the cells are blank or they contain some text. Is it possible to consider blank cells and texts as zeros by default? I want this operation to include cells that haven’t been filled up yet, and might be filled in the future, or could be filled with text. And the operation I described was just an example, I want the rows to extend to its limit. Is there are way to do this.

I’m sorry if this question is trivial, I’m a new to LibreOffice Calc and spreadsheets.

[anon73440385]

Hello

open a Calc sheet and go to Tools -> Options -> LibreOffice Calc -> Formula -> Category: Detailed Calculation Settings and tick Custom (conversion of text to numbers and more) and click on Details;

On Details dialog select “Treat as zero”

[Lupp]

I would suggest to never set the option to generally treat texts as zero values. It is global to the document (at least) and creates a risk to make serious errors “invisible”.
It’s too bad anyway that references to blank cells are treated as zero in many cases while they are ignored by COUNT(), AVERAGE() and the like. (Lots of bad traditions. Inconsistencies.)

-1- Where you work with basically mathematical means (in the stronger sense) try to avoid any mixture of texts and numbers (and blank cells) from the beginning.
-2- If you decide otherwise for important reasons use formulae giving you full control.
In the given case =MMULT(IFERROR(C5:F5/ISNUMBER(C5:F5);0);TRANSPOSE(IFERROR(C7:F7/ISNUMBER(C7:F7);0))) can do this for you, e.g. However, the formula given in the OQ is unnecessarily complicated. The matrix product of a vector with the transposed result of an equally dimensioned vector is equivalent to the inner (scalar) product of these vectors, and for the scalar product spreadsheets have the specialised function SUMPRODUCT(). Moreover: SUMPRODUCT() is treating text and blank cells as zero values. (This unfortunately without a sufficiently clear specification.)