I want get a completely empty cell at return without using quotes, i.e. not like at previous question
Ctrl + arrow is fetches an empty string as two double quotes. What I do not want as return of function. Is there a way to do so?
Yet another case is pivot table. It adds (empty) result.
It is in principle impossible. The return value of a formula is shown in the very same cell, which contains the formula. And because this cell contains the formula it is never “empty”. Otherwise a formula would erase itself.
If the empty string is a valid result and you need to indicate, that the formula result is not valid, then you can use the function NA(). It generates the special error value #N/A, which can be tested by the function ISNA().
If you need to erase the content of a cell dependent on the values of other cells, you need a macro to do that.
Thank you!
There are comments and answers also discussing workarounds. (See my own answer of today e.g.)
In order to provide an answer, here’s my two cents that actually do exactly what is asked.
If you’re doing a conditional on, let’s say, cell B4:
then you can do this:
=IF(B4=0;{};B4)
This results in a blank cell if the value of B4 is zero and returns B4 when it is not.
Best,
L
The cell containing the formula will answer FALSE if asked =ISBLANK(TheCell). It will also return FALSE to =ISTEXT(TheCell) and to =ISNUMBER(). We may consider this “virtually blank”. The fact, however, that the cell is containing a formula is unchangeable - and to =ISFORMULA(TheCell) we get a TRUE.
In my test with V4.2.2 the “pseudoblank” result of the suggested formula persisted when the value of B4 was changed to something <>0. I regard this as a bug.
Coming next to reproducing B4 even if BLANK may be the array formula {=OFFSET(B4,0,0,1,1)} in TheCell. It will, however, also answer TRUE to =ISFORMULA(TheCell) and FALSE to =ISBLANK(TheCell).
Still Ctrl + arrow is fetches cell with ={} although it looks empty. Interesting enough pivot table recognizes ={} as zero and ="" as second (empty) whereas totally empty as the first (empty).
I wanted a way to make a cell, that contains a function to do calculations based on the content of another cell, look “empty” when that other cell didn’t contain any value.
This is the perfect answer for me. I wasn’t aware of the {} construction to make a cell look “empty”.
Great tip!
We can use {} in a function to get an empty cell as result. Brilliant! This is exactly was i was searching for.
Thank You. If i could i would up-vote Your answer, especially as the other above is basically wrong.
Excellent, {} works!
This thread is very old, but having a subject that was clearly (though not fully consistent) worded, it is visited again and again. I originally contributed a soultion only by commenting on the answer posted as “wiki” on 2015-04-29.
Meanwhile there was opportunity to learn that this anwer was unnecessarily complicated. The usage of OFFSET() or similar constructs can be omitted if not the calculated reference is needed. Thus I give a new answer with one old and unchangeable part “-1-” and the simple way to get something like a soultion as far as possible in part “-2-”.
-1-
To get an actually blank cell by a resident formula is strictly impossible because the formula itself is content of the cell.
-2-
The simple way to get what’s next to blank if the referenced cell (A2 e.g.) is blank is the formula {=A2:A2}.
(This is =A2:A2 entered for array-evaluation either by Ctrl+Shift+Enter or from the formula wizard.)
The answer is exemplified in this attachment.
The short answer seems to be no.
Following on Regina’s logic answer (a cell containing a formula is not empty because it contains the formula): because a cell returning “” is interpreted as containing Text you can use an IF(ISTEXT(A1);0;A1) condition to solve (some) problems.
I asked about returning empty cell because I want to use Ctrl + arrow. Not for using formula. Formula do well with empty cells.
In order to provide an answer, here’s my two cents that actually do exactly what is asked.
If you’re doing a conditional on, let’s say, cell B4:
then you can do this:
=IF(B4=0;{};B4)
This results in a blank cell if the value of B4 is zero and returns B4 when it is not.
Best,
L
Hi
My answer may be too late for this, sorry, but if the goal is:
Ctrl+Arrow should not find a cell displaying “” (result of a formula)
a solution could be to protect these cells (eg . apply a cell style) then Tools▸Protect▸Sheet▸uncheck Allow all users to Select protected cells.
Regards
I do not have a solution, but an opinion: THIS SHOULD BE SOLVED ADDING EXTRA VALUE, for an example EMPTY() which means that will be considered like 0 (zero) in every calculationts and will always look like empty. Solutions like =IF(B4=0;{};B4) or =IF(B4=0;"";B4) does not solve the problem, if the function is in cell A2, it must be calculated on cell A4: =A2+A3 now it returns #VALUE!. What is a bit amazing is that functtion =SUM(A2:A3) can calculate the value 0, even if A2 is {} or “”, but =A2+A2 cannot do this.