For example =IF(G3<>0,G3/J2,)
My goal is to have a cell be either a calculated value or the equivalent of zero but without any digits displayed. Then to be able to use that cell in another cells math formula.
In AOO I was able to do =IF(G3<>0,G3/J2,"") and the null string would be accepted as zero if the cell were used in another formula. With Libre the null string will not be accepted in a mathematical formula.
You could have =IF(G3<>0,G3/J2,0) and format that cell as number with Format code of # . The zero won’t be visible in the cell
In AOO I was able to do =IF(G3<>0,G3/J2,"") and the null string would be accepted as zero if the cell were used in another formula. With Libre the null string will not be accepted in a mathematical formula
It must work just as well. But if you configure detailed calculation settings under Options|Calc|Formula to treat conversion from text to number as error, you may have the problem you report…
I’ve learnt something new today. Thank you Mike
Though treating on-the-fly conversion from text to number always as #VALUE! error (in those detailed calculation settings) would be best practice as it indicates errors early.
Format code of #
Displays only integer values for values other than 0. Better would be
General;-General; (note the trailing semicolon to form an empty display string for 0 value) or
0.00;-0.00; for two decimals, for example.
You can never assign to a cell. To “assign” a value to a cell, you need a formula in the cell. But then the cell has a formula and therefore is not empty. The only way to get an empty cell is, to mark the cell and press backspace-key and then ‘Delete all’.
The empty string is not visible, but still it is a string. It is up to the formula, which expects the cell, what is does with an empty string. For example =ISBLANK(A1) will return FALSE, if the value in cell A1 is an empty string, but =COUNTBLANK(A1) will return 1. You need to examine the definition of a function.
If you want, that an empty string is treated as zero, then you need to write the formula accordingly. When you have a specific example, we can help you to construct the formula.
It is slightly different between Excel and Clac, you can simply remove the quote sign where you want empty cell but it will still show as zero because its a value but I think this will still work:
In the function edit it to =IF(G3<>0,G3/J2,)
Then in the cell formatting enter: [>0]0.000;[<0]-0.000;"";@
This will hide zeros without causing errors if you need the cell in other calculations.
This does exactly what I needed. Although I did use the simpler cell format of 0.000;-0.000;