Ask Your Question

How to display blank cell instead of #value

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

bigphillyman gravatar image

updated 2016-03-09 21:14:27 +0200

Alex Kemp gravatar image

I'm entering the following formula into cell D6:


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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-01-27 07:04:13 +0200

ROSt52 gravatar image

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

edit flag offensive delete link more

answered 2015-01-27 07:17:21 +0200

karolus gravatar image

updated 2015-01-27 07:18:03 +0200

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

edit flag offensive delete link more


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

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

answered 2016-12-06 19:10:39 +0200

Glake gravatar image

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 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 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?

edit flag offensive delete link more


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.

Lupp gravatar imageLupp ( 2016-12-06 21:22:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 3,656 times

Last updated: Dec 06 '16