Ask Your Question

# How to display blank cell instead of #value

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?

edit retag close merge delete

## 3 Answers

Sort by » oldest newest most voted

follow @ROSt52 , but instead of " " use "" , because the cell will look empty but isn't.

more

## Comments

@karolus - You are right the ""version might be the better solution.

( 2015-02-02 01:24:16 +0200 )edit

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?

more

## Comments

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.

( 2016-12-06 21:22:42 +0200 )edit

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

more

## Stats

Asked: 2015-01-26 21:19:28 +0200

Seen: 3,645 times

Last updated: Dec 06 '16