I’m entering the following formula into cell D6:
=IF(C16>=-5,C16+10,null)
This is resulting in #value, as C16 is blank.
Cell C16 currently has a formula in it. Is this what is causing the problem and is there no way around it until there is a value in C16, or is there some way to have D6 show blank until both formulas have a value?
The problem is in the string null
If your write "null" the cell displays null
If you want nothing to appear for c16<-5 replace "null" by " " ( there is a space between the quotation marks) the cell will look empty for c16<-5
I am having the same problem with displaying a “null” answer for an IF-statement. The old instructions suggest using “” as the comments here. The LibreOffice Vanilla 5.2.3.5 will display #VALUE for the simplest addition of three cells when one contains “”. I think it is either a bug or programmer’s idea in this new program. This is an old thread and probably was a solution a year ago.
I have used “” for years in Excel, OpenOffice, and LibreOffice. Now with LibreOffice Vanilla 5.2.3.5 on a Mac Book Pro with an i7 CPU and 16GB Ram, the value of “” will give the result of #VALUE as the answer of a simple =A1+A2+A3 if any of the entries have the “” content or result of an IF statement where “” is my false result.
Any ideas? I really need this or something similar to keep the spreadsheet view clean.
I saw on a post where someone suggested using {} instead of “”. That seemed to work but the IF statements with several additions, multiplications, and divisions would fail to process. No Error, Just silent. This spreadsheet of about 30 x 36 cells with about half of them equations and and about a third of the cells having entered values. While using the {} alternative, the last 70 IF-statements would not complete. They would just sit uncalculated until I go into the FunctionHelper and it would complete that one calculation. Although sometimes part of the equation would just disappear. I removed all of the {} and went back to “” and #VALUE. Suggestion, ideas, alternatives, or work-arounds?
That the operand “+” shall return an error indicator if at least one operand is not numeric is explicitly specified by OpenFormula. Accepted as ‘numeric’ are numbers, references to blank cells and texts accepted for conversion into numbers under the syntax applicable in the current locale: If you want referenced texts (now without any exceptions) and blank cells to be ignored, use the SUM() function.