Created attachment 120137 [details] Empty cells not always treated as zero in formulas “Empty” cells that are empty as the result of a formula are not treated as zero in formulas referencing them, but throw an #VALUE! error instead. Simple sample (see attached spreadsheet) Cell A1: =1*B1 Cell B1: =IF(0,"","") Cell A2: =1*B2 Cell B2: Cell A3: =1*B3 Cell B3: =B1 Cell A4: =1*B3 Cell B4: =B2 Result: Cell A1 and A3 display #VALUE! Cell A2 and A4 display 0 Expected Result: Cell A1 to A4 display 0 Hope this is easy to fix as this is preventing me from migrating me to LibreOffice (from OpenOffice); lots of my spreadsheets contain empty cells as the result of a formula that are later again referenced in other formula. Note: I tested this with LibreOffice 3.3.0, 3.6.7.2, 4.0.0.1, 4.4.6.3, and 5.0.2.2. All except 3.3.0 exhibit this behaviour so it is a problem that was introduced quite early on.
Hi @Havo, a cell with a formula is never empty, it has an empty string, but cell is not empty. LibreOffice have became more restrictive about how strings are treated in calculations. I think only direct references with no dubious conversion in number are treated as such. In the last versions there is an option on how treat an empty string for calculations. Menu/Tools/Options/LibreOffice calc/Formulas/Detailed calculation settings - Treat empty string as zero. You can also involve the reference with N() function. " Returns the numeric value of the given parameter. Returns 0 if parameter is text or FALSE. If an error occurs the function returns the error value. " I think we can resolve as notabug, please if you are not agree, reopen it.
(In reply to m.a.riosv from comment #1) > Hi @Havo, > a cell with a formula is never empty, it has an empty string, but cell is > not empty. True, but I knew of no other way of creating an empty value when using a formula than by using the empty string. > LibreOffice have became more restrictive about how strings are treated in > calculations. I think only direct references with no dubious conversion in > number are treated as such. I understand why LibreOffice has become more strict, and in general this is a good idea. It just bit me a bit hard in this case. And you're right, LibreOffice still does treat the completely empty cell as zero (Heck, even a cell with a simple formula like =A6 where cell A6 is empty is displayed as 0). > In the last versions there is an option on how treat an empty string for > calculations. > > Menu/Tools/Options/LibreOffice calc/Formulas/Detailed calculation settings - > Treat empty string as zero. Awesome! This fully solves my issue. I did check all the available options, but hadn't looked under the “Details…” (doh!) > You can also involve the reference with N() function. > " > Returns the numeric value of the given parameter. Returns 0 if parameter is > text or FALSE. > If an error occurs the function returns the error value. > " Nice! I didn't know this function yet and will use it in the future (so I can use the more strict calculation rules) > I think we can resolve as notabug, please if you are not agree, reopen it. Fully agree, sorry to have bothered you! Your response, and the way LibreOffice is heading in its development, has further convinced me to fully migrate to LibreOffice though, so it wasn't in total vain ;-)
NOTE for others who stumble on this page... > Menu/Tools/Options/LibreOffice calc/Formulas/Detailed calculation settings - Treat empty string as zero This does not take effect immediately, nor does recalculate (F9) work. I found I had to close the file and reopen it for the setting to take effect.