Ask Your Question

Can I setup a spreadsheet to show a blank when result is zero? [closed]

asked 2018-04-20 23:26:24 +0100

restrepo gravatar image

updated 2018-04-23 12:18:16 +0100

David gravatar image

I have a cell format specified as... [$$-409]#,###K . When the result is 1000, it displays $1,000K. However, when the result is ZERO, it displays $K. How can I get this specific spreadsheet (Not Calc in general for all spreadsheets) to display a blank cell instead?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-04-26 13:05:46.074884

2 Answers

Sort by » oldest newest most voted

answered 2018-04-20 23:43:20 +0100

Lupp gravatar image

updated 2018-04-20 23:46:37 +0100

(Obviously the question is not about printing, but about cell formats in general whether for printing or for the screen view.)

A Numbers format code has basically 3 entries, separated by semicolons, for results that actually are numbers:
First entry for positive results, second entry for negative results (default like positive with "-" prefixed), third entry applied if result is zero (default like positive).

$#K;-$#K;"" will display $500K for result=500, -$123K for result=-123, and an empty cell if result=0.

(BTW: The comma as thousands separator is deprecated because of expectable conflicts with the internationally frequent use of the comma as the decimal separator. What is the "K" in you fomat code supposed to mean?)

edit flag offensive delete link more


Right on Lupp. Thanks

restrepo gravatar imagerestrepo ( 2018-04-21 00:28:49 +0100 )edit

@restrepo: It sounds like this answer was helpful, so please mark it correct, as explained under guidelines for asking.

Jim K gravatar imageJim K ( 2018-04-21 16:09:26 +0100 )edit

Thanks, that works for me.

Arniearm gravatar imageArniearm ( 2018-04-23 11:33:52 +0100 )edit

comma as thousands separator is deprecated because of expectable conflicts

That's not true, used in format codes they are perfectly fine. When stored as ODF a description of the format is stored, not a copy of the format code string. Storing an en-US #,### will load as #.### in a de-DE environment.

erAck gravatar imageerAck ( 2018-04-23 16:08:37 +0100 )edit

Writing "deprecated" I referred to ISO 31-0 Sect. 3.3: "Numbers consisting of long sequences of digits can be made more readable by separating them into groups, preferably groups of three, separated by a small space. For this reason, ISO 31-0 specifies that such groups of digits should never be separated by a comma or point, as these are reserved for use as the decimal sign." (Quoted from Highlighting by @Lupp .)

Lupp gravatar imageLupp ( 2018-04-23 17:54:01 +0100 )edit

answered 2018-04-26 01:23:01 +0100

restrepo gravatar image

updated 2018-04-26 01:23:47 +0100

Thanks to erAck. It would have preferred to use the words "somewhat inaccurate" instead of "not true" which implies a lie. No need to come across as overly aggressive. At the end, Lupp's tip solved the problem. Thanks again to both erAck and Lupp.

edit flag offensive delete link more


That wasn't meant as being aggressive or implicating a lie. I assumed that Lupp was talking of interoperability problems between locales with different separators (which do exist for example for the TEXT() function's second argument format string). There was no other context indicated for the "deprecated". Also bear in mind that most of us are not native English speakers, including me.

erAck gravatar imageerAck ( 2018-04-26 13:05:02 +0100 )edit

Question Tools

1 follower


Asked: 2018-04-20 23:26:24 +0100

Seen: 95 times

Last updated: Apr 26 '18