Since some recent upgrade of openSUSE’s LibreOffice packages (I’m using v5.3.3.2 at the moment and the trouble likely started when I switched to Leap 42.3), I’ve been fighting formula errors in some complex sheets. Sheets that have been working for ages, before the upgrade.
It quickly turned out that for some unknown reason, LibreOffice Calc decided to suddenly stop treating empty cells as “0”, making many Calc functions return an error. The only problem for me was: Is this a bug or am I doing something wrong?
Searching the web left me with the impression that LibreOffice should generally treat these empty cells as zeros. But after the problem persisted even after some later minor version upgrades of the packages (and I could not spot any bug reports on this issue), I started digging deeper, instead of trying to work around the issue by updating thousands of currently empty cells.
And indeed, it turned out to be a configurable option it LibreOffice Calc, well hidden from the casual observer: The LibreOffice registry knows about a value “EmptyStringAsZero”.
<item oor:path="/org.openoffice.Office.Calc/Formula/Syntax"><prop oor:name="EmptyStringAsZero" oor:op="fuse"><value>false</value></prop></item>
But where’s the place to change this via the user interface? After some digging, I found it via the “Options” menu, in the “LibreOffice Calc” settings for formulas, in the “detailed calculation settings”. For some reason, during some migration of my age-old user settings for LibreOffice, this was set to “custom”, with its values hiding behind the “details” button in that page of the dialog.
Et voilà: There’s a checkbox to toggle treating empty strings as zeros. Once that was checked, all errors in my sheets vanished and I was back to a usable set of sheets.
And of course, once I knew what I was looking for, I found several hits on the issue, including a description of the setting itself – from 2013. So while this blog entry is definitely redundant, maybe it will help others find information on the issue easier than I did…