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?
(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?)
Right on Lupp. Thanks
@restrepo: It sounds like this answer was helpful, so please mark it correct, as explained under guidelines for asking.
Thanks, that works for me.
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.
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 ISO 31-0 - Wikipedia. Highlighting by @Lupp .)
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.
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.