Having trouble changing settings concerning the treatment of empty cells in calc

[erikvbattum]

I’m trying to perform calculations on a pretty large array in LibreOffice calc. This array has a number of cells that are empty. In calculations I want these cells to be treated as zeros.

from earlier threads I found out that there is a setting for this. I went to Tools>Options>LibreOffice calc>Formula>Detailed Calculation Settings. Here default settings was enabled, so I ticked custom. I opened the details menu next to custom and under ‘conversion from text to number’ I selected ‘treat as zero’ and I ticked ‘treat empty string as zero’. I clicked okay to exit the box and in the options menu clicked apply and okay.

This changed nothing for my calculations and when I reopened the options menu, everything was set back the way it was before. I’m pretty sure it is something really simple that I’m doing wrong but I can’t find it.

Thanks in advance!

[keme1]

AFAIK, the settings you mention do not apply to empty cells, but to cells containing text data. A blank or empty string is not the same as an empty cell, even though the visual appearance may be identical.

Could you upload a sample spreadsheet file displaying your issue?

If you can’t, then at least provide some more detail:

• What formula do you have?
• What does your data set look like?

Regardless of what detail or upload you provide, it is also useful to know …

• what result do you get from your formula?
  and …
• what result did you expect?

[LeroyG]

@erikvbattum

treat empty string as zero

Link to the Help on Detailed Calculation Settings.

[Villeroy]

There is a setting to hide zeroes which could be a wanted effect for financial accounting.
The general rule is that blank cells are equivalent to zero. =A1=0 => TRUE and you can calculate with that value as in =A1+1. Since a cell has a text and a value at the same time =A1="" is also true.
Aggregations such as count, min, max, average etc. ignore blank cells. This can be overridden with an array function. For instance the array function =MIN(N(A1:B4)) returns zero if A1:B4 contains positive numbers and blanks.