Page 1 of 2 12 LastLast
Results 1 to 16 of 18

Thread: LibreOffice - hide data in calculated fields

  1. #1
    Senior Member Giraffe's Avatar
    Join Date
    Feb 2007
    Location
    near Northampton
    Posts
    830
    Thanks
    34
    Thanked
    39 times in 34 posts
    • Giraffe's system
      • Motherboard:
      • Gigabyte GA-Z97-D3H
      • CPU:
      • Intel Core i5 5675C, S 1150, Broadwell, Quad Core, 3.1GHz, 3.6GHz Turbo
      • Memory:
      • Crucial Ballistix DDR3 1600, 4GB
      • Storage:
      • 512GB Samsung SSD, 850 Pro, SATA 3
      • Graphics card(s):
      • Integrated
      • PSU:
      • Seasonic S12G 450W 80+ Gold
      • Case:
      • Antec P100
      • Operating System:
      • Windows 7 Pro, 32-bit
      • Monitor(s):
      • Dell U2415, 24", 16:10
      • Internet:
      • TP-Link modem-router; 3mbps sync.

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

    Political lubricant:

    Rocket WMD45

  2. #2
    The late but legendary peterb - Onward and Upward peterb's Avatar
    Join Date
    Aug 2005
    Location
    Looking down & checking on swearing
    Posts
    19,381
    Thanks
    2,892
    Thanked
    3,403 times in 2,693 posts

    Re: LibreOffice - hide data in calculated fields

    Do you mean remain blank for a negative number or any number?
    (\__/)
    (='.'=)
    (")_(")

    Been helped or just 'Like' a post? Use the Thanks button!
    My broadband speed - 750 Meganibbles/minute

  3. #3
    Senior Member Lanky123's Avatar
    Join Date
    Jul 2007
    Location
    Oxford
    Posts
    914
    Thanks
    90
    Thanked
    148 times in 99 posts
    • Lanky123's system
      • Motherboard:
      • Gigabyte GA-H81M-D2V
      • CPU:
      • Core i5 4570
      • Memory:
      • 2 x 4GB Vengeance LP
      • Storage:
      • 250GB Samsung 840 EVO SSD + 2+4TB HDD + 3TB Synology DS216SE
      • Graphics card(s):
      • MSI Radeon R9 270X HAWK
      • PSU:
      • Silverstone Strider 400W
      • Case:
      • Silverstone Sugo SG02B-F
      • Operating System:
      • Windows 8.1 / Ubuntu 16.04
      • Monitor(s):
      • ElectriQ 32" 4k IPS + Dell 22" U2212HM
      • Internet:
      • Virgin 60Mbit/s

    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;"")

  4. #4
    Senior Member Giraffe's Avatar
    Join Date
    Feb 2007
    Location
    near Northampton
    Posts
    830
    Thanks
    34
    Thanked
    39 times in 34 posts
    • Giraffe's system
      • Motherboard:
      • Gigabyte GA-Z97-D3H
      • CPU:
      • Intel Core i5 5675C, S 1150, Broadwell, Quad Core, 3.1GHz, 3.6GHz Turbo
      • Memory:
      • Crucial Ballistix DDR3 1600, 4GB
      • Storage:
      • 512GB Samsung SSD, 850 Pro, SATA 3
      • Graphics card(s):
      • Integrated
      • PSU:
      • Seasonic S12G 450W 80+ Gold
      • Case:
      • Antec P100
      • Operating System:
      • Windows 7 Pro, 32-bit
      • Monitor(s):
      • Dell U2415, 24", 16:10
      • Internet:
      • TP-Link modem-router; 3mbps sync.

    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!
    PeterC

    Political lubricant:

    Rocket WMD45

  5. #5
    The late but legendary peterb - Onward and Upward peterb's Avatar
    Join Date
    Aug 2005
    Location
    Looking down & checking on swearing
    Posts
    19,381
    Thanks
    2,892
    Thanked
    3,403 times in 2,693 posts

    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.
    (\__/)
    (='.'=)
    (")_(")

    Been helped or just 'Like' a post? Use the Thanks button!
    My broadband speed - 750 Meganibbles/minute

  6. #6
    The late but legendary peterb - Onward and Upward peterb's Avatar
    Join Date
    Aug 2005
    Location
    Looking down & checking on swearing
    Posts
    19,381
    Thanks
    2,892
    Thanked
    3,403 times in 2,693 posts

    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
    (\__/)
    (='.'=)
    (")_(")

    Been helped or just 'Like' a post? Use the Thanks button!
    My broadband speed - 750 Meganibbles/minute

  7. Received thanks from:

    Giraffe (18-09-2018)

  8. #7
    Senior Member Giraffe's Avatar
    Join Date
    Feb 2007
    Location
    near Northampton
    Posts
    830
    Thanks
    34
    Thanked
    39 times in 34 posts
    • Giraffe's system
      • Motherboard:
      • Gigabyte GA-Z97-D3H
      • CPU:
      • Intel Core i5 5675C, S 1150, Broadwell, Quad Core, 3.1GHz, 3.6GHz Turbo
      • Memory:
      • Crucial Ballistix DDR3 1600, 4GB
      • Storage:
      • 512GB Samsung SSD, 850 Pro, SATA 3
      • Graphics card(s):
      • Integrated
      • PSU:
      • Seasonic S12G 450W 80+ Gold
      • Case:
      • Antec P100
      • Operating System:
      • Windows 7 Pro, 32-bit
      • Monitor(s):
      • Dell U2415, 24", 16:10
      • Internet:
      • TP-Link modem-router; 3mbps sync.

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

    Political lubricant:

    Rocket WMD45

  9. #8
    The late but legendary peterb - Onward and Upward peterb's Avatar
    Join Date
    Aug 2005
    Location
    Looking down & checking on swearing
    Posts
    19,381
    Thanks
    2,892
    Thanked
    3,403 times in 2,693 posts

    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.
    (\__/)
    (='.'=)
    (")_(")

    Been helped or just 'Like' a post? Use the Thanks button!
    My broadband speed - 750 Meganibbles/minute

  10. #9
    Senior Member
    Join Date
    Jul 2009
    Location
    West Sussex
    Posts
    1,076
    Thanks
    73
    Thanked
    132 times in 124 posts
    • kompukare's system
      • Motherboard:
      • Asus P8Z77-V LX
      • CPU:
      • Intel i5-3570K
      • Memory:
      • 2 x 8GB Crucial Ballistix Elite PC3-14900
      • Storage:
      • Crucial MX200 | Sandisk Extreme 120GB SSD | WDC 1TB Green | Samsung 1Tb Spinpoint
      • Graphics card(s):
      • Sapphire R9 290 VaporX 7950
      • PSU:
      • Antec 650 Gold TruePower (Seasonic) or Seasonic SII-330
      • Case:
      • Aerocool DS 200 (silenced, 53.6 litres)l)
      • Operating System:
      • Windows 10-64
      • Monitor(s):
      • 2 x Dell P2414H

    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.

  11. Received thanks from:

    Giraffe (19-09-2018),peterb (18-09-2018)

  12. #10
    The late but legendary peterb - Onward and Upward peterb's Avatar
    Join Date
    Aug 2005
    Location
    Looking down & checking on swearing
    Posts
    19,381
    Thanks
    2,892
    Thanked
    3,403 times in 2,693 posts

    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!
    (\__/)
    (='.'=)
    (")_(")

    Been helped or just 'Like' a post? Use the Thanks button!
    My broadband speed - 750 Meganibbles/minute

  13. #11
    Senior Member Giraffe's Avatar
    Join Date
    Feb 2007
    Location
    near Northampton
    Posts
    830
    Thanks
    34
    Thanked
    39 times in 34 posts
    • Giraffe's system
      • Motherboard:
      • Gigabyte GA-Z97-D3H
      • CPU:
      • Intel Core i5 5675C, S 1150, Broadwell, Quad Core, 3.1GHz, 3.6GHz Turbo
      • Memory:
      • Crucial Ballistix DDR3 1600, 4GB
      • Storage:
      • 512GB Samsung SSD, 850 Pro, SATA 3
      • Graphics card(s):
      • Integrated
      • PSU:
      • Seasonic S12G 450W 80+ Gold
      • Case:
      • Antec P100
      • Operating System:
      • Windows 7 Pro, 32-bit
      • Monitor(s):
      • Dell U2415, 24", 16:10
      • Internet:
      • TP-Link modem-router; 3mbps sync.

    Re: LibreOffice - hide data in calculated fields

    Quote Originally Posted by kompukare View Post
    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.
    PeterC

    Political lubricant:

    Rocket WMD45

  14. #12
    The late but legendary peterb - Onward and Upward peterb's Avatar
    Join Date
    Aug 2005
    Location
    Looking down & checking on swearing
    Posts
    19,381
    Thanks
    2,892
    Thanked
    3,403 times in 2,693 posts

    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)
    (\__/)
    (='.'=)
    (")_(")

    Been helped or just 'Like' a post? Use the Thanks button!
    My broadband speed - 750 Meganibbles/minute

  15. Received thanks from:

    Giraffe (19-09-2018)

  16. #13
    Senior Member Giraffe's Avatar
    Join Date
    Feb 2007
    Location
    near Northampton
    Posts
    830
    Thanks
    34
    Thanked
    39 times in 34 posts
    • Giraffe's system
      • Motherboard:
      • Gigabyte GA-Z97-D3H
      • CPU:
      • Intel Core i5 5675C, S 1150, Broadwell, Quad Core, 3.1GHz, 3.6GHz Turbo
      • Memory:
      • Crucial Ballistix DDR3 1600, 4GB
      • Storage:
      • 512GB Samsung SSD, 850 Pro, SATA 3
      • Graphics card(s):
      • Integrated
      • PSU:
      • Seasonic S12G 450W 80+ Gold
      • Case:
      • Antec P100
      • Operating System:
      • Windows 7 Pro, 32-bit
      • Monitor(s):
      • Dell U2415, 24", 16:10
      • Internet:
      • TP-Link modem-router; 3mbps sync.

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

    Political lubricant:

    Rocket WMD45

  17. #14
    Senior Member Giraffe's Avatar
    Join Date
    Feb 2007
    Location
    near Northampton
    Posts
    830
    Thanks
    34
    Thanked
    39 times in 34 posts
    • Giraffe's system
      • Motherboard:
      • Gigabyte GA-Z97-D3H
      • CPU:
      • Intel Core i5 5675C, S 1150, Broadwell, Quad Core, 3.1GHz, 3.6GHz Turbo
      • Memory:
      • Crucial Ballistix DDR3 1600, 4GB
      • Storage:
      • 512GB Samsung SSD, 850 Pro, SATA 3
      • Graphics card(s):
      • Integrated
      • PSU:
      • Seasonic S12G 450W 80+ Gold
      • Case:
      • Antec P100
      • Operating System:
      • Windows 7 Pro, 32-bit
      • Monitor(s):
      • Dell U2415, 24", 16:10
      • Internet:
      • TP-Link modem-router; 3mbps sync.

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

    Political lubricant:

    Rocket WMD45

  18. #15
    The late but legendary peterb - Onward and Upward peterb's Avatar
    Join Date
    Aug 2005
    Location
    Looking down & checking on swearing
    Posts
    19,381
    Thanks
    2,892
    Thanked
    3,403 times in 2,693 posts

    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
    (\__/)
    (='.'=)
    (")_(")

    Been helped or just 'Like' a post? Use the Thanks button!
    My broadband speed - 750 Meganibbles/minute

  19. #16
    Senior Member Giraffe's Avatar
    Join Date
    Feb 2007
    Location
    near Northampton
    Posts
    830
    Thanks
    34
    Thanked
    39 times in 34 posts
    • Giraffe's system
      • Motherboard:
      • Gigabyte GA-Z97-D3H
      • CPU:
      • Intel Core i5 5675C, S 1150, Broadwell, Quad Core, 3.1GHz, 3.6GHz Turbo
      • Memory:
      • Crucial Ballistix DDR3 1600, 4GB
      • Storage:
      • 512GB Samsung SSD, 850 Pro, SATA 3
      • Graphics card(s):
      • Integrated
      • PSU:
      • Seasonic S12G 450W 80+ Gold
      • Case:
      • Antec P100
      • Operating System:
      • Windows 7 Pro, 32-bit
      • Monitor(s):
      • Dell U2415, 24", 16:10
      • Internet:
      • TP-Link modem-router; 3mbps sync.

    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!
    PeterC

    Political lubricant:

    Rocket WMD45

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •