[Solved] Display "0", not blank, if result is 0

Discuss the spreadsheet application

[Solved] Display "0", not blank, if result is 0

Postby No3Jacks » Wed Jan 03, 2018 4:37 pm

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
Last edited by No3Jacks on Thu Jan 04, 2018 3:05 pm, edited 2 times in total.
OpenOffice 4.1 on Windows 7
No3Jacks
 
Posts: 11
Joined: Mon Aug 25, 2014 11:46 pm

Re: Display "0", not blank, if result is 0

Postby Lupp » Wed Jan 03, 2018 5:41 pm

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.
Last edited by Lupp on Wed Jan 03, 2018 8:19 pm, edited 2 times in total.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1874
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Display "0", not blank, if result is 0

Postby Zizi64 » Wed Jan 03, 2018 7:15 pm

Another possible solution:
Code: Select all   Expand viewCollapse view
=IF(E35="";0;E35-D35)
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.5 and AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 6878
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display "0", not blank, if result is 0

Postby RusselB » Wed Jan 03, 2018 8:38 pm

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
Code: Select all   Expand viewCollapse view
=if(E35=0;0;E35-D35)
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4410
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display "0", not blank, if result is 0

Postby No3Jacks » Wed Jan 03, 2018 9:06 pm

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
No3Jacks
 
Posts: 11
Joined: Mon Aug 25, 2014 11:46 pm

Re: Display "0", not blank, if result is 0

Postby RusselB » Wed Jan 03, 2018 10:07 pm

Code: Select all   Expand viewCollapse view
=if(isblank(e35);"";d35-e35)

Keep the setting specified in Lupp's post as checked.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4410
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display "0", not blank, if result is 0

Postby RusselB » Wed Jan 03, 2018 10:09 pm

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
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4410
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display "0", not blank, if result is 0

Postby No3Jacks » Wed Jan 03, 2018 11:24 pm

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
OpenOffice 4.1 on Windows 7
No3Jacks
 
Posts: 11
Joined: Mon Aug 25, 2014 11:46 pm

Re: Display "0", not blank, if result is 0

Postby RusselB » Thu Jan 04, 2018 1:06 am

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
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4410
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display "0", not blank, if result is 0

Postby No3Jacks » Thu Jan 04, 2018 2:26 am

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
OpenOffice 4.1 on Windows 7
No3Jacks
 
Posts: 11
Joined: Mon Aug 25, 2014 11:46 pm

Re: Display "0", not blank, if result is 0

Postby RusselB » Thu Jan 04, 2018 5:00 am

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.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4410
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display "0", not blank, if result is 0

Postby No3Jacks » Thu Jan 04, 2018 5:26 am

Jan 06 2018.ods
(22.73 KiB) Downloaded 20 times
OpenOffice 4.1 on Windows 7
No3Jacks
 
Posts: 11
Joined: Mon Aug 25, 2014 11:46 pm

Re: Display "0", not blank, if result is 0

Postby robleyd » Thu Jan 04, 2018 5:43 am

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
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1740
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Display "0", not blank, if result is 0

Postby RusselB » Thu Jan 04, 2018 6:14 am

While I see the problem, I'm at a loss to explain why it is happening.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4410
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display "0", not blank, if result is 0

Postby robleyd » Thu Jan 04, 2018 6:35 am

Appears to be the formatting - leading zeros is set to none. Set to one and zeros display.
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1740
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Display "0", not blank, if result is 0

Postby Zizi64 » Thu Jan 04, 2018 8:37 am

Appears to be the formatting - leading zeros is set to none. Set to one and zeros display.


Leading zeros.png


I suggest you to use Cell styles instead of the direct (manual) formatting.
Last edited by Zizi64 on Thu Jan 04, 2018 4:02 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.5 and AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 6878
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display "0", not blank, if result is 0

Postby No3Jacks » Thu Jan 04, 2018 3:04 pm

[Solved] Thanks to all who helped. Chuck
OpenOffice 4.1 on Windows 7
No3Jacks
 
Posts: 11
Joined: Mon Aug 25, 2014 11:46 pm

Re: [Solved] Display "0", not blank, if result is 0

Postby robleyd » Thu Jan 04, 2018 11:40 pm

Via PM the OP advised that formatting was in fact causing the issue.
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1740
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests