How do you make a formula that can return a blank value?

[Llelan D.]

How do you, in a cell formula, return a blank value?

For example, a formula for cell A1: IF(A2 >= 0; A2; <blank value>)
If the numeric value of cell A2 is >= 0, the value of A2 is returned, but if the value of A2 is < 0, a <blank value> is returned.
A <blank value> would be a value such that the function ISBLANK(A1) returns TRUE.

Omitting the last optional argument returns the BOOLEAN value FALSE.
Omitting any value to the last argument (IF(A2 >= 0; A2;)) returns Err:511 when A2 < 0.
Using "" returns an empty text value and not a blank value.

I can find no documentation on literal formula operands.
There is no NULL, Null, null, BLANK, Blank, or blank literal value, or a BLANK() function.
I can find no formula function to delete a cell value (ie: render it BLANK).
I can find no formula function that cancels the formula if the tested value is false. In other words: if the tested value is false, the formula does not alter the target cell value.

I am looking for an actual verified answer, not arcane work-arounds nor untested guesses. I'm not looking to configure OpenOffice so that it does not display some other value making it look blank, I need the value to actually be blank so that the ISBLANK(A1) function returns true.

It can not be possible to have a formula language without the ability to assign any valid value, including the initial blank value. There must be an undocumented literal blank value.

A simple way to test this is to assign =IF(A2 >= 0; A2; <blank value>) to A1 (replace <blank value> with something that returns a blank value) or a function that does not alter A1 if A2 < 0, a numeric value < 0 to A2, and the function =ISBLANK(A1) to A3. A successful test will show nothing in cell A1 and TRUE in cell A3.

Any help would be greatly appreciated.

[Zizi64]

=IF(A2 >= 0; A2;"")
Test it with "ISNUMBER" function, not "ISBLANK" function .

OOo Help:

ISBLANK
Returns TRUE if the reference to a cell is blank. This function is used to determine if the content of a cell is empty. A cell with a formula inside is not empty.
If an error occurs, the function returns a logical or numerical value.

[acknak]

It can not be possible to have a formula language without the ability to assign any valid value, including the initial blank value.

Hah. Spreadsheets do all sorts of things that are impossible to imagine.

You cannot have a formula whose result is the same (in all cases) as if the formula was never there. Yes, it can make things messy. As Tibor already suggested, you have to use some other test, or have the formula produce an empty string and then test for that. An empty cell, or a cell that contains an empty string, will compare equal, as will comparing them to numeric zero.

This leads to some bizarre situations, like two values comparing equal to a third value, but not to each other: A = C and B = C, but A ≠ B.

Because they are messy and tend to cause problems, I prefer to avoid assigning any particular meaning to an empty cell. I use a custom cell format to display nothing, if that's important, but the cell should contain some definite value.

It might help if we had some idea of what you were trying to do.

[Llelan D.]

Thx zizi64, the existence of a formula is now all confused with a cell value so you can not reproduce the original blank value.

The concepts behind a spreadsheet were supposed to reflect the same concepts of a database field. You can have a value of a give type or no value: Null in the case of a database field, and Blank in a spreadsheet cell. In a database, you can query if the field is a set value or synthetic (returned by calculation or real-time measurement). This was supposed to also be reflected in a spreadsheet cell, but apparently lazy coders have mucked it all up. This might make it easier for a spreadsheet coder, but harder for a spreadsheet user, the opposite of what was desired.

At least I know what to do now and why. Thanks!

[ehin]

So, Llelan were you able to return a BLANK value? or what is the workaround you did?

I too am exasperated that i cannot return a BLANK value or HIGH VALUE for that matter in my logical IF tests.

[acknak]

It might be helpful if you can briefly describe what you're trying to do. There may be a different approach.

[keme]

A few possible workarounds:
If you need to return a value which can be seen as equivalent to a blank cell, just have your formula return the empty string ("").
=(A1="") will return TRUE if A1 is truly blank (empty cell) or contains a formula returning an empty string. Spaces or zero in the cell returns FALSE.
To also see spaces as equivalent to blank, use the TRIM() function. =(TRIM(A1)="")
(Some users will use the spacebar to "clear" a cell.)

If you need a specific return value saying "nothing available/applicable/appropriate", there is a particular error value designated for that: #N/A.
Use the function NA() to return that value, and ISNA() to check for it.

[roger_dodger]

Llelan D - Have you found an answer to your query?
I'm having the same issue.
Can use NA(), which return an error so that when charting, excel will ignore. However, won't work in pivot tables or charts.
Have you had any joy since you posted this ( I notice some time ago)

[Lupp]

(Sorry! No use in trying an answer to this old question in only few words. The situation actually is complicated.)
(My answer does, of course, not apply to "Office 2013" - Excel I suppose - which I never saw.)

There already was stated that there is nothing like a "blank value". What comes next to it is the empty text.
However, there are cases where we want a cell to behave even more as if it was blank, in specific in the sense that it shall answer FALSE if we ask it ISNUMBER and also FALSE if we ask it ISTEXT. (It cannot answer FALSE on ISFORMULA without ignoring logic.)
Questions to the same effect as discussed here were posted in all the fora on "our software" I visit on a regular basis now and then.
I also once tried to put the problem in clear words as best I could, and posted the question in https://ask.libreoffice.org/en.
The (partial) solution discussed in the bottom part of the example document attached here was offered by "m.a.riosv" there. (I then still thought OFFSET only did the thing if used in array mode, and this is still the only solution I can accept as consistent in LibreOffice).

What comes next to an actual "copy of contents" of a cell in LibreOffice (at least sine V4), also allowing for a decision whether the original cell was blank, is {=OFFSET(ReferOriginal;0;0;1;1)} (array mode).
To test the result for being "pseudoblank" you may then use =NOT(OR(ISTEXT(ReferFacsimile);ISNUMBER(ReferFacsimile)))

If you want to deliver a "pseudoblank" result under conditions things get messed up completely at present. Workarounds I tested do not act as expected. This neither using AOO nor using LibO.

A truley consistent solution to our problem can not be found without introducing a new "value" like #PSEUDOBLANK! and specifying that it should produce results like a blank cell except for FORMULA and ISFORMULA .

The attached demo was last edited and saved with AOO4.1.2. To learn about the relevant differences you should also open it with a recent LibO. Otherwise you only get demonstrated the inconsistent solutions in the bottom part where a value of 0 is returned but ISNUMBER is answered FALSE.
The examples (Regard A1:N7 !) demonstrate the suggested solutions.
They work differently in AOO as compared with LibO except, possibly, in old "Legacy" versions.
This bevaviour is NOT specified in any mandatory document, and may therefore be subject to changes without notice from version to version.
It is, however, more likely in my opinion that a consistent implementation will not be changed with this respect.

I seriously beg to get informed (jag[well known character]psilosoph.de) if you experience a change with this respect in the behaviour of either AOO or LibO!