How to return null/empty from function in calc

English
, , ,
#1

Hello,
In previous versions of LibreOffice calc (up to and including 3.5.x) it was easy to have semi-blank field as a result of a function (for example “if”):

=if(a1=0;1;"")

then if a1 wasn’t 0 we had empty cell – was working ok, no problems.
But starting with 3.6.x (including recently released 4.0.0) it is somehow broken - returning “” causes to misbehave counting functions as well as pivot tables (which then fallbacks to text summary even if the source cell formating is number or even time – highly annoying.

tl;dr
how to return null/empty/non-value form function? I’ve tried:

=if(a1=0;1;)
=if(a1=0;1;null)
=if(a1=0;1;NULL)

and nothing seems to works

EDIT:
Don’t know why I cannot comment or particular answer so here it is: ma original problem comes from this bug report https://bugs.freedesktop.org/show_bug.cgi?id=54651 as indicated there default returned value messes up PivotTable.

  • @mariosv - unfortunatelly your solution doesn’t work as i get “0” instead of empty cell (so it’s same as =if(true;1;0), but it causes PivotTable to show correct values
  • @Edward3 - in your case ignoring optional argument causes the cell to display “0” (it’s time formatted) thus not what I want (i want to have empty cell)
  • @ROSt53 - please set format of the cell to time and it will display “00:00” instead of empty cell…
How to return empty cell from function in calc
#2

Don’t know why I cannot comment or particular answer so here it is: ma original problem comes from this bug report https://bugs.freedesktop.org/show_bug.cgi?id=54651 as indicated there default returned value messes up PivotTable

#3
  • @mariosv - unfortunatelly your solution doesn’t work as i get “0” instead of empty cell (so it’s same as =if(true;1;0), but it causes PivotTable to show correct values
#4
  • @Edward3 - in your case ignoring optional argument causes the cell to display “0” (it’s time formatted) thus not what I want (i want to have empty cell)
#5
  • @ROSt53 - please set format of the cell to time and it will display “00:00” instead of empty cell…
#6

@Wojtek – 10 karma points for adding comments to answers.

#7

@manj_k Please watch our for corporate compliance if you offer bribes of 10 karma points

#8

@wojtek To which cell do you want me to set format “time”? Did you make the same what I showed? If so could you please show screen shots or attach your file? I deleted mine after the test.

#9

OP: @Wojtek

#12

As for Version 3.6.5.2 (Build ID: 5b93205) on XP/SP3 I made a test and found that all works as I understand manj_k’s question.

a1 = 0 or empty results in:

image description

image description

a1 = 1 or -1 results in:

image description

image description

Hope I understood the original question right.

#10

There is a new option in 4.0, I do not know if it can work for you.

image description
image description1029×603 118 KB

#11

I can’t upvote you because of karma, but this solved my identical problem perfectly! Thank you!

#13

=IF(A1=0,1) should work. The OtherwiseValue is optional

IF(Test; ThenValue; OtherwiseValue)
Test is any value or expression that can be TRUE or FALSE.
ThenValue (optional) is the value that is returned if the logical test is TRUE.
OtherwiseValue (optional) is the value that is returned if the logical test is FALSE.
In the LibreOffice Calc functions, parameters marked as “optional” can be left out only when no parameter follows.

#14

=IF(A1=0,1) do not return blank value. Insterd it return FALSE value. Not what topic starter whanted.

#15

While hardly elegant, you should be able to specify a string (as the otherwise value if the condition is false) which can then be replaced with nothing using find/replace.
If(A1=1,1,“ABCDEFG”),
Find/Replace ABCDEFG with nothing (blank field), searching in Values (not in Formulas).

#16

It worked fine for me.

Thanks so much

David Zafra

#17

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.