LibreOffice - hide data in calculated fields
If I have a simple formula for cells in a column, e.g. x-y=z, then when I enter the values for x and y I get z; however, if x hasn't been entered I get z=-y. This, of course, happens whatever the formula.
What I would like is for z to remain as a blank cell. This would look better and also I could print out the page for manual use.
I've tried searching - trouble is, I don't know the terms to use and therefore can't ask the question.
Re: LibreOffice - hide data in calculated fields
Do you mean remain blank for a negative number or any number?
Re: LibreOffice - hide data in calculated fields
I don't use spreadsheets much so there might be a simpler solution, but you could try using an IF statement to test if x is a number? https://help.libreoffice.org/Calc/Logical_Functions#IF
For example, this should return A1-B1 if A1 is a number, blank otherwise. You might need to ensure A1 is explicitly formatted as a number rather than text.
Code:
=IF(ISNUMBER(A1);A1-B1;"")
Re: LibreOffice - hide data in calculated fields
Thanks for the replies.
Either blank or zero would be better than a nonsensical value.
I'll look at the IF method. I was thinking IF but couldn't see how to do it. The irony is that, about 14 - 15 years ago, in a Lotus Approach database, we had 14 nested IFs! I needed a lot of help with that one, especially as it was difficult to discern '" from '''.
After I come back from the pub - not!
Re: LibreOffice - hide data in calculated fields
Yes, the IF method is the way to do it - I’ve got a spreadsheet in libre/open office with something similar - I’ll dig it out.
Re: LibreOffice - hide data in calculated fields
Code:
=IF (B224=0;0;B224-B223)
This goes in the relevant cell
If Cell B244=0 then set the cell to zero, else set it to B224-B223
Obviously change the conditions/action to suit your application
Re: LibreOffice - hide data in calculated fields
That rings a bell! (actually, if it does, I've got it wrong!). I'll have go...
OK, that does it - puts in £0 or 0.00%.
I had a go at returning a blank cell, based on dim memory of ISBLANK, but it didn't work. If I decide that I want a paper version I'll have another go, but for now I'm happy, thank you.
Re: LibreOffice - hide data in calculated fields
I think you could set up a style to that - but thats way beyond my ability - you would set the text colour to be the same as the cell background colour.
Re: LibreOffice - hide data in calculated fields
Actually, IF doesn't have to return numbers as it's equally able to return strings, so peterb's solution is about there.
Something like this:
Code:
=IF (B224=0;"";B224-B223)
That should get you an empty string instead of the 0.
Re: LibreOffice - hide data in calculated fields
It does! Simples - I spent 15 minutes trying to sort that before I gave up and posted my solution above! :)
Re: LibreOffice - hide data in calculated fields
Quote:
Originally Posted by
kompukare
Actually, IF doesn't have to return numbers as it's equally able to return strings, so peterb's solution is about there.
Something like this:
Code:
=IF (B224=0;"";B224-B223)
That should get you an empty string instead of the 0.
That works on simple subtraction however, for %age
=IF (B41=0;"";B41-6695)/6695
returns #VALUE!
Tried formatting it a Number rather than Percent but it doesn't change.
Re: LibreOffice - hide data in calculated fields
I think you have your nesting brackets in the wrong place
Try
Code:
=IF (B41=0;"";(B41-6695)/6695)
Re: LibreOffice - hide data in calculated fields
Ah yes, of course - separate and conquer; brackets - count 'em in and count 'em out.
All is now working properly, thank you very much to all.
Re: LibreOffice - hide data in calculated fields
Sorry - I'm back. This is just for neatness, if it can be done:
Where there's an increasing value down a column it's easy to get the total, but trying to get the blank "" works OK but then Copy - Paste down the column increments the cell co-ordinates, of course.
I can do it with =IF ((HT4-180.76)<=0,"",(HT4-180.76)) where 187.76 is the starting value (would be cell HT3 in this case) - easy to do every 6 months but untidy.
I've been trying to find out how to make HT3 a fixed reference, so that it goes, e.g. =IF ((HT4-HT3)<=0,"",(HT4-HT3)) then on the next row
=IF ((HT5-HT3)<=0,"",(HT5-HT3)), rather than HT3 becoming HT4.
Any idea if it's possible, please. If it's no, I'll leave it as it is.
Re: LibreOffice - hide data in calculated fields
HT$3
will stop it incrementing - always references that cell (but will increment across columns)
$HT$3 makes it a static reference across columns and rows
Re: LibreOffice - hide data in calculated fields
Great, thanks! I spent some time on LO Help last night and didn't find it.
All this has reminded me of how much 'fun' it can be! :help: