LibreOffice - hide data in calculated fields

[Giraffe]

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.

[peterb]

Do you mean remain blank for a negative number or any number?

[Lanky123]

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.

=IF(ISNUMBER(A1);A1-B1;"")

[Giraffe]

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!

[peterb]

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.

[peterb]

=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

[Giraffe]

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.

[peterb]

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.

[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:

=IF (B224=0;"";B224-B223)

That should get you an empty string instead of the 0.

[peterb]

It does! Simples - I spent 15 minutes trying to sort that before I gave up and posted my solution above!

[Giraffe]

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:

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

[peterb]

I think you have your nesting brackets in the wrong place

Try

=IF (B41=0;"";(B41-6695)/6695)

[Giraffe]

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.

[Giraffe]

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.

[peterb]

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

[Giraffe]

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!