Ask Your Question

Considering blank cells and text as zeros in MMULT

asked 2019-04-30 15:42:45 +0200

someguywantinghelp gravatar image

updated 2020-07-29 23:50:19 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-04-30 17:31:29 +0200

Lupp gravatar image

updated 2019-04-30 17:36:06 +0200

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.)

edit flag offensive delete link more

answered 2019-04-30 16:40:33 +0200

Opaque gravatar image


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;

image description

On Details dialog select "Treat as zero"

image description

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-30 15:42:45 +0200

Seen: 371 times

Last updated: Apr 30 '19