Ask Your Question
1

How to return null/empty from function in calc [closed]

asked 2013-02-07 15:45:08 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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... 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
  • @Edward - 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)
  • @ROSt52 - please set format of the cell to time and it will display "00:00" instead of empty cell...
edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-25 05:39:55.284810

Comments

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

Wojtek gravatar imageWojtek ( 2013-02-18 09:39:29 +0200 )edit
  • @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
Wojtek gravatar imageWojtek ( 2013-02-18 09:39:52 +0200 )edit
  • @Edward - 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)
Wojtek gravatar imageWojtek ( 2013-02-18 09:39:59 +0200 )edit
  • @ROSt52 - please set format of the cell to time and it will display "00:00" instead of empty cell...
Wojtek gravatar imageWojtek ( 2013-02-18 09:45:46 +0200 )edit

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

manj_k gravatar imagemanj_k ( 2013-02-18 10:12:52 +0200 )edit

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

ROSt52 gravatar imageROSt52 ( 2013-02-18 11:00:34 +0200 )edit

@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.

ROSt52 gravatar imageROSt52 ( 2013-02-18 11:02:55 +0200 )edit
manj_k gravatar imagemanj_k ( 2013-10-22 19:46:16 +0200 )edit

5 Answers

Sort by » oldest newest most voted
1

answered 2013-02-08 04:05:25 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

edit flag offensive delete link more
0

answered 2013-02-07 23:31:51 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

edit flag offensive delete link more

Comments

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

Donaithnen gravatar imageDonaithnen ( 2015-08-10 18:22:02 +0200 )edit
0

answered 2013-02-08 17:53:40 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

=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.

edit flag offensive delete link more

Comments

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

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-14 10:41:56 +0200 )edit
0

answered 2013-03-01 10:49:06 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

It worked fine for me.

Thanks so much

David Zafra

edit flag offensive delete link more
0

answered 2013-02-18 20:42:33 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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).

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-02-07 15:45:08 +0200

Seen: 17,856 times

Last updated: Mar 01 '13