Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 28 Sep 2019 12:09:22 +0200How to return empty cell from function in calchttps://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/I want get a completely empty cell at return without using quotes, i.e. not like at previous [question](http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/)Wed, 14 May 2014 11:07:11 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/Comment by denis.polkovnikov for <p>I want get a completely empty cell at return without using quotes, i.e. not like at previous <a href="http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/">question</a></p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=33895#post-id-33895Ctrl + 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?Wed, 14 May 2014 11:20:19 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=33895#post-id-33895Comment by denis.polkovnikov for <p>I want get a completely empty cell at return without using quotes, i.e. not like at previous <a href="http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/">question</a></p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=34684#post-id-34684Yet another case is pivot table. It adds (empty) result.Wed, 28 May 2014 06:47:41 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=34684#post-id-34684Answer by LudoIII for <p>I want get a completely empty cell at return without using quotes, i.e. not like at previous <a href="http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/">question</a></p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=50074#post-id-50074In 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 Wed, 29 Apr 2015 22:38:49 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=50074#post-id-50074Comment by denis.polkovnikov for <p>In order to provide an answer, here's my two cents that actually do exactly what is asked.</p>
<p>If you're doing a conditional on, let's say, cell B4:</p>
<p>then you can do this:
=IF(B4=0;{};B4)</p>
<p>This results in a blank cell if the value of B4 is zero and returns B4 when it is not.</p>
<p>Best,
L </p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=62247#post-id-62247Still 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).Wed, 23 Dec 2015 15:26:41 +0100https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=62247#post-id-62247Comment by Lupp for <p>In order to provide an answer, here's my two cents that actually do exactly what is asked.</p>
<p>If you're doing a conditional on, let's say, cell B4:</p>
<p>then you can do this:
=IF(B4=0;{};B4)</p>
<p>This results in a blank cell if the value of B4 is zero and returns B4 when it is not.</p>
<p>Best,
L </p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=50077#post-id-50077The 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.Wed, 29 Apr 2015 23:39:35 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=50077#post-id-50077Comment by Lupp for <p>In order to provide an answer, here's my two cents that actually do exactly what is asked.</p>
<p>If you're doing a conditional on, let's say, cell B4:</p>
<p>then you can do this:
=IF(B4=0;{};B4)</p>
<p>This results in a blank cell if the value of B4 is zero and returns B4 when it is not.</p>
<p>Best,
L </p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=50078#post-id-50078Coming 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).Wed, 29 Apr 2015 23:45:22 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=50078#post-id-50078Comment by josef64 for <p>In order to provide an answer, here's my two cents that actually do exactly what is asked.</p>
<p>If you're doing a conditional on, let's say, cell B4:</p>
<p>then you can do this:
=IF(B4=0;{};B4)</p>
<p>This results in a blank cell if the value of B4 is zero and returns B4 when it is not.</p>
<p>Best,
L </p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=187274#post-id-187274We 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.Mon, 18 Mar 2019 14:44:30 +0100https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=187274#post-id-187274Comment by GeertVc for <p>In order to provide an answer, here's my two cents that actually do exactly what is asked.</p>
<p>If you're doing a conditional on, let's say, cell B4:</p>
<p>then you can do this:
=IF(B4=0;{};B4)</p>
<p>This results in a blank cell if the value of B4 is zero and returns B4 when it is not.</p>
<p>Best,
L </p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=177886#post-id-177886I 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!Tue, 01 Jan 2019 09:01:55 +0100https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=177886#post-id-177886Comment by Toby for <p>In order to provide an answer, here's my two cents that actually do exactly what is asked.</p>
<p>If you're doing a conditional on, let's say, cell B4:</p>
<p>then you can do this:
=IF(B4=0;{};B4)</p>
<p>This results in a blank cell if the value of B4 is zero and returns B4 when it is not.</p>
<p>Best,
L </p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=210478#post-id-210478Excellent, {} works!Sat, 28 Sep 2019 12:09:22 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=210478#post-id-210478Answer by Lupp for <p>I want get a completely empty cell at return without using quotes, i.e. not like at previous <a href="http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/">question</a></p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=177896#post-id-177896*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](/upfiles/15463429291128794.ods)** attachment. Tue, 01 Jan 2019 12:46:02 +0100https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=177896#post-id-177896Answer by Regina for <p>I want get a completely empty cell at return without using quotes, i.e. not like at previous <a href="http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/">question</a></p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=33903#post-id-33903It 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.Wed, 14 May 2014 13:10:27 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=33903#post-id-33903Comment by Lupp for <p>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.</p>
<p>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().</p>
<p>If you need to erase the content of a cell dependent on the values of other cells, you need a macro to do that.</p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=177897#post-id-177897There are comments and answers also discussing workarounds. (See my own answer of today e.g.)Tue, 01 Jan 2019 12:49:14 +0100https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=177897#post-id-177897Comment by denis.polkovnikov for <p>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.</p>
<p>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().</p>
<p>If you need to erase the content of a cell dependent on the values of other cells, you need a macro to do that.</p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=34171#post-id-34171Thank you!Mon, 19 May 2014 12:09:55 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=34171#post-id-34171Answer by Pedro for <p>I want get a completely empty cell at return without using quotes, i.e. not like at previous <a href="http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/">question</a></p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=33904#post-id-33904The 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.Wed, 14 May 2014 13:25:26 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=33904#post-id-33904Comment by denis.polkovnikov for <p>The short answer seems to be no.</p>
<p>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.</p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=34172#post-id-34172I asked about returning empty cell because I want to use Ctrl + arrow. Not for using formula. Formula do well with empty cells.Mon, 19 May 2014 12:15:09 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?comment=34172#post-id-34172Answer by pierre-yves samyn for <p>I want get a completely empty cell at return without using quotes, i.e. not like at previous <a href="http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/">question</a></p>
https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=69626#post-id-69626Hi
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`.
RegardsTue, 10 May 2016 12:01:42 +0200https://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/?answer=69626#post-id-69626