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

[No3Jacks]

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

[Lupp]

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.

[Zizi64]

Another possible solution:

Code: Select all

=IF(E35="";0;E35-D35)

[RusselB]

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

=if(E35=0;0;E35-D35)

[No3Jacks]

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.

[RusselB]

Code: Select all

=if(isblank(e35);"";d35-e35)

Keep the setting specified in Lupp's post as checked.

[RusselB]

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

[No3Jacks]

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

[RusselB]

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

[No3Jacks]

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

[RusselB]

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.

[No3Jacks]

Jan 06 2018.ods

(22.73 KiB) Downloaded 116 times

[robleyd]

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?

[RusselB]

While I see the problem, I'm at a loss to explain why it is happening.

[robleyd]

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

[Zizi64]

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

I suggest you to use Cell styles instead of the direct (manual) formatting.

[No3Jacks]

[Solved] Thanks to all who helped. Chuck

[robleyd]

Via PM the OP advised that formatting was in fact causing the issue.