Ask Your Question
0

My simple formulas no longer give blank cells [closed]

asked 2014-01-12 23:42:37 +0200

RCM gravatar image

updated 2016-02-18 14:18:43 +0200

Alex Kemp gravatar image

I have used simple formulas like =IF(B10>0,+B10-B9,"") for years and have always returned a blank cell. Worked fine in Libreoffice until a recent update and now it will not return a blank cell, instead it returns zeros. I see where I can turn off " show zero values" but then I can't have other cells where I want to display zero as a value. Is there a way to return to showing a blank cell instead of zeros while retaining the ability to display zeros where I choose?

Added comment:Using windows 8.1 and Libre Version: 4.1.4.2 Build ID: 0a0440ccc0227ad9829de5f46be37cfb6edcf72

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-18 14:19:17.197874

Comments

Please what is your Operating System and the LibreOffice version?, I can't reproduce your issue.

m.a.riosv gravatar imagem.a.riosv ( 2014-01-13 00:18:36 +0200 )edit

Is there a decimal issue? In your cell that shows zero, can you add more decimals. I put 0.0001 in both B9 and B10, then changed C10 to display no decimals which resulted in a zero value? This doesn't explain why it would suddenly change though.

Rugslug gravatar imageRugslug ( 2014-01-13 01:22:46 +0200 )edit

amusing this formula ...=IF(B10>0,+B10-B9,"")...is in cell B11... If B10 is blank then B11 has 0.00 in it,if I put a zero in B10 then B11 goes blank.

Also if I use Ctrl drag to copy the formula to adjacent cells in column the zeros go away.

BUT when I close the doc and then reopen it the zeros have returned .

These spread sheets work normally with these exact formulas for well over a year.

RCM gravatar imageRCM ( 2014-01-13 01:57:17 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2014-01-13 14:39:24 +0200

L-user gravatar image

I tried in LibreOffice 4.1.4.2 and I can't replicate this problem. There must something be wrong with your document. Did you use some formatting or something? Can you open a new document and try to replicate the problem? If problem persists then you may changed the template.

edit flag offensive delete link more
0

answered 2014-01-13 04:37:04 +0200

Rugslug gravatar image

By "recent update", what LO version were you at prior to this "anomaly"? 4.1.3 or 4.0.x or 3.x.x? As my bad memory serves me, it's possible that something was changed in LO with regards to the way it assesses a cell's value and then uses it in a math calculation...something to do with being consistant with excel. Again, bad memory on my part.

With a zero in B10 then the statement result is False, therefore blank. When a cell is blank, LO might be assessing it as text? If the cell B10 has a zero, but the cell is formatted as text, the result is (TRUE) b10-b9 and not a blank.

Perhaps turn on Value Highlighting in Tools, Options, +LibreOffice Calc, View. Numbers are shown as: Blue, Formulae: Green, Text: Black

This is not an answer, but I wanted to attached a screen shot.

C:\fakepath\if statement.png

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-01-12 23:42:37 +0200

Seen: 1,050 times

Last updated: Jan 13 '14