[Solved] Display "0", not blank, if result is 0
[Solved] Display "0", not blank, if result is 0
Can't seem to figure this one out.
=IF(E35="";E35;E35-D35)
When the result of E35-D35 is 0 a blank cell is displayed. I would like the cell to display the 0 or option 2 would be to display "E" (for Even) if the result is zero.
Thanks,
Chuck
=IF(E35="";E35;E35-D35)
When the result of E35-D35 is 0 a blank cell is displayed. I would like the cell to display the 0 or option 2 would be to display "E" (for Even) if the result is zero.
Thanks,
Chuck
Last edited by No3Jacks on Thu Jan 04, 2018 3:05 pm, edited 2 times in total.
OpenOffice 4.1 on Windows 7
Re: Display "0", not blank, if result is 0
Most likely you need to check 'Tools' > 'Options' > 'OpenOffice Calc' > 'View' > 'Display' > 'Zero values'.
Editing after reading more thoroughly: Do as Zizi64 suggests. My answer did not address the actual issue. I don't delete it, however, because the hint at least is slightly related to the question.
Editing after reading more thoroughly: Do as Zizi64 suggests. My answer did not address the actual issue. I don't delete it, however, because the hint at least is slightly related to the question.
Last edited by Lupp on Wed Jan 03, 2018 8:19 pm, edited 2 times in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Display "0", not blank, if result is 0
Another possible solution:
Code: Select all
=IF(E35="";0;E35-D35)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Display "0", not blank, if result is 0
Please note that in your original formula and Tibor's, the check is E35="" which is different from E35 being blank and might be different from E35 having a zero value
Since the query was for when the result is 0, then I'd suggest
Since the query was for when the result is 0, then I'd suggest
Code: Select all
=if(E35=0;0;E35-D35)
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Display "0", not blank, if result is 0
Thanks Tibor. Almost there. If E35 is blank I need F35 to be blank. Right now it's showing 0 using your formula =IF(E35="";0;E35-D35). In other words if there's nothing in E35 then F35 is blank, if E35 is not blank and E35-D35 equals 0 then F35 should show 0. Using Lupps recommendation above Display Zero values is checked.
OpenOffice 4.1 on Windows 7
Re: Display "0", not blank, if result is 0
Code: Select all
=if(isblank(e35);"";d35-e35)
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Display "0", not blank, if result is 0
Note: It is not possible for a formula to return a literal blank in a cell.
The closest you can get is using something like I just posted
The closest you can get is using something like I just posted
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Display "0", not blank, if result is 0
Thanks RusselB. Thanks for your patience. Just want to be sure I have this correct. I can display 0 or blank in F, it's one or the other, not either or. In the example below for ANTHONY I'd like F to show 0 (E-D). However if E were blank then F should be blank. Thanks again. I can live with the recommendation above, I'm just trying to have my cake and eat it too. Chuck
A B C D E F
NAME GHIN $10 TARGET 12/30 +/-
ANTHONY, 31 29 31 31 0
ANTHONY, 31 29 31
A B C D E F
NAME GHIN $10 TARGET 12/30 +/-
ANTHONY, 31 29 31 31 0
ANTHONY, 31 29 31
OpenOffice 4.1 on Windows 7
Re: Display "0", not blank, if result is 0
The formula I gave you checks if the cell in E is blank or not.
If the cell is blank (no text, no number, no formula), then it returns a "" which appears to be blank.
If it is not blank, then the mathematics is performed.
If the mathematics results in 0, with the option checked that Lupp specified, then you'll see the 0
If the cell is blank (no text, no number, no formula), then it returns a "" which appears to be blank.
If it is not blank, then the mathematics is performed.
If the mathematics results in 0, with the option checked that Lupp specified, then you'll see the 0
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Display "0", not blank, if result is 0
January 6, 2018
A B C D E F G H I
NAME GHIN $10 TARGET 01/06 +/- PLACE $ SKINS $ TOTAL $
30 31 31 31
Your formula: =if(isblank(e35);"";e35-d35) is in F and 'Tools' > 'Options' > 'OpenOffice Calc' > 'View' > 'Display' > 'Zero values' (Zero values is checked). Unfortunately when D & E are the same IE (31) E - D returns blank not 0 as pictured above.
You and I have probably spent more time than it's worth trying to figure it out. Thanks for your time. Chuck
A B C D E F G H I
NAME GHIN $10 TARGET 01/06 +/- PLACE $ SKINS $ TOTAL $
30 31 31 31
Your formula: =if(isblank(e35);"";e35-d35) is in F and 'Tools' > 'Options' > 'OpenOffice Calc' > 'View' > 'Display' > 'Zero values' (Zero values is checked). Unfortunately when D & E are the same IE (31) E - D returns blank not 0 as pictured above.
You and I have probably spent more time than it's worth trying to figure it out. Thanks for your time. Chuck
OpenOffice 4.1 on Windows 7
Re: Display "0", not blank, if result is 0
Please upload a sample spreadsheet so that we can see exactly what you are working with.
Having the data as you have been giving it doesn't seem to be working, possibly because there is a lot of information stored in the spreadsheet which is different on my system, and, therefore, can be causing different results.
To attach a file, click the PostReply button (not QuickReply) and use the dialog located under the area where your reply would go.
Having the data as you have been giving it doesn't seem to be working, possibly because there is a lot of information stored in the spreadsheet which is different on my system, and, therefore, can be causing different results.
To attach a file, click the PostReply button (not QuickReply) and use the dialog located under the area where your reply would go.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Display "0", not blank, if result is 0
None of the cells in E3:E39 have a value; is it possible that you actually want the formulae that are currently in column F to be in column E? In other words, do you want to check, in column E, if Target is blank and if not, display the result of subtracting $10 from Target?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Display "0", not blank, if result is 0
While I see the problem, I'm at a loss to explain why it is happening.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Display "0", not blank, if result is 0
Appears to be the formatting - leading zeros is set to none. Set to one and zeros display.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Display "0", not blank, if result is 0
I suggest you to use Cell styles instead of the direct (manual) formatting.Appears to be the formatting - leading zeros is set to none. Set to one and zeros display.
Last edited by Zizi64 on Thu Jan 04, 2018 4:02 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Display "0", not blank, if result is 0
[Solved] Thanks to all who helped. Chuck
OpenOffice 4.1 on Windows 7
Re: [Solved] Display "0", not blank, if result is 0
Via PM the OP advised that formatting was in fact causing the issue.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers