Ask Your Question
0

Considering blank cells and text as zeros in MMULT

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

someguywantinghelp 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
2

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

Lupp gravatar image

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

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
0

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

Opaque gravatar image

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;

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

Stats

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

Seen: 96 times

Last updated: Apr 30