The use case of this is for when you want to format a set of cells in a column (most frequently but not limited to) with a format such as "# ##0,??". This becomes very comfortable for reading the numbers in the column because a) you only get the amount of decimals you want, b) you can easily compare the values visually because each digit type (namely hundredths, tenths, units, tens, hundreds, etc.) is vertically aligned with the others (at least when you align right all the cells), and c) integers can be displayed without trailing zeroes and still be aligned. However, when a number only has an integer part, the decimal separator is still displayed after it. This looks strange since without decimals the decimal separator loses purpose: it separates no decimals.
(In reply to Severo Raz from comment #0) > The use case of this is for when you want to format a set of cells in a > column (most frequently but not limited to) with a format such as "# > ##0,??". > ... > However, when a number only has an integer part, the decimal separator is > still displayed after it. This looks strange since without decimals the > decimal separator loses purpose: it separates no decimals. Why not just add-in the zeros? So this: 1543.34 2345 5668.1 Becomes this: 1543.34 2345.00 5668.10 In some cases, sig figs are relevant to the final outcome, but I think the best practice there would be to use scientific notation. Status -> NEEDINFO [cc: jay]
This seems to me more of a number format issue and whether LO supports such a format. If LO supports the format and its not shown correctly, then that is a bug that should be fixed.
(In reply to Yousuf (Jay) Philips from comment #2) > This seems to me more of a number format issue and whether LO supports such > a format. If LO supports the format and its not shown correctly, then that > is a bug that should be fixed. Indeed Yousuf, thank you for your input (In reply to Robinson Tryon (qubit) from comment #1) > Why not just add-in the zeros? > > So this: > > 1543.34 > 2345 > 5668.1 > > Becomes this: > > 1543.34 > 2345.00 > 5668.10 > > In some cases, sig figs are relevant to the final outcome, but I think the > best practice there would be to use scientific notation. Well having the zeroes not show is the whole purpose of this bug report, isn't it?
I tried the same formula in gnumeric and wps spreadsheets and the result was the same as in libreoffice. I believe that if you dont want the decimal point to appear because the number is an integer, there might be some thing that you can add to the formula that would give you that result. Did some searching online and found someone asking the same thing for excel. http://answers.microsoft.com/en-us/office/forum/office_2007-excel/removing-decimal-places-on-whole-currency-numbers/90fcc899-4a70-454b-ac58-7e013edca950?auth=1
Excel does it the same, the decimal separator is always displayed. Quite likely we won't do it differently..
Well it's an unfortunate thing that no other spreadsheet processor can get this particular deed done right. However, consider this: in Calc, when the format # ##0,## format is used, the comma is not displayed; it seems awfully inconsistent that when the format # ##0,?? is used the comma does show. This looks like a bug to me, and perhaps even a lovebug. I will reopen this, because not fixing an issue *based* solely on whether the issue is present on other office suites I regard as pure mediocrity, and perhaps you should as well. The fact that LibreOffice is a free office suite, and for now most probably considered an alternative office suite, is absolutely no reason to condemn it to live in MS Office's shadow.
(In reply to Severo Raz from comment #6) > However, consider this: in Calc, when the format # ##0,## format is used, > the comma is not displayed; it seems awfully inconsistent that when the > format # ##0,?? is used the comma does show. This looks like a bug to me, > and perhaps even a lovebug. Yes it seems strange that the comma is being retained when comparing it, but i'm not a number format expert. :D So #,##0.## shows [ 1,234] [ 1,234.1] [ 1,234.12] And #,##0.?? shows [ 1,234. ] [ 1,234.1 ] [ 1,234.12] And you think it should show [ 1,234 ] [ 1,234.1 ] [ 1,234.12] @Stuart, @Heiko, @Cor: Can you give your input on this? > I will reopen this, because not fixing an issue *based* solely on whether > the issue is present on other office suites I regard as pure mediocrity, and > perhaps you should as well. Yes it shouldnt be solely based on whether its the same in other office suites, but compatibility with other office suites is important.
Created attachment 136664 [details] The file with the problem and workaround. I have been dealing with the addition of units to decimal numbers in Calc. This is the description of my problem (You may want to skip this): When I need to add a unit to a whole number, I do the following: Format → Cells → Number → Format Code → “# ##0" V"” → OK The value for “1” becomes “1 V”, which is OK. When I need to add a unit to a decimal number, I do the following: Format → Cells → Number → Format Code → “# ##0,###" V"” → OK The value for “0,1” becomes “0,1 V”, which is OK as well. But when I need to add a unit to a whole or a decimal number (based on the result of the formula, for example), I do the same as above: Format → Cells → Number → Format Code → “# ##0,###" V"” → OK The value for “0,1” becomes “0,1 V”, this one is OK. The value for “0” becomes “0, V”, but this one is not. This is the workaround (You may need to read the description above): So, when I need something universal that will work for both cases, the only way to do so (that I can see) is by using conditional formatting: (Select a SINGLE cell) → Format → Condition... → Condition 1 → Cell value is → equal to → INT([Current cell, you can copy it from the Range: field if you have selected one cell only]) → Apply Style → New Style... → Numbers → (Select the first (user-defined) style (for whole numbers).) → Add → Condition 2 → Cell value is → not equal to → INT([Current cell, you can copy it from the Range: field if you have selected one cell only]) → Apply Style → New Style... → Numbers → (Select the second (user-defined) style (for decimal numbers).) → OK To apply that for other cells, I copy that cell and use Ctr + Shift + V to paste only formats, this way it's possible to paste the format to multiple cells at a time. Just in case your're a developer willing to change the issue: May you please add a conditional decimal separator, simple checkbox, a formatting condition (something like “[IS_DECIMAL]# ##0,###" V";[IS_WHOLE]# ##0" V"”), or something like that? Or, perhaps, other GUI feature that will apply my workaround automatically? (Which I suppose will not break compatibility.) I am very sorry for my poor English, feel free to correct me if you don't mind. Thank you very much.
General #,##0" V" #,##0.###" V" 1 (=a2) 1 V 1 V 2.1 (=a3) 2 V 2.1 V 3.123 (=a4) 3 V 3.123 V What is missing?
The case the original report actually was about. #,##0.?? creates a format that reserves a blank space for trailing zero digits after the decimal separator. With integer values the decimal separator is still displayed, but shouldn't. 1.23 => "1.23" 1.2 => "1.2 " 1 => "1. " should be "1 " Comment 8 seems to talk about some earlier version where that might had happened with .## as well, it doesn't now (and also not in 5.3 fwiw).
@Laurent: maybe something for you?
Note that for comment 5 I tested in Excel and it does not omit the decimal separator. I can't make up my mind whether we should or not. There is some logic in it that the separator is always displayed with .?? (blanks instead of 0, preserving columns with a fixed-width font) but not with .## (digit and place omitted if 0), and my guess is that once we changed this to not display the separator we'd get a bug report of some angry Excel document user that we did so.. YMMV..
So.. made up my mind over night ;) and came to a conclusion. As all major implementations display the separator we should not change behaviour. Closing.
(In reply to Eike Rathke from comment #11) > @Laurent: maybe something for you? Hi Eike, I think that format ##.?? should always display decimal separator, even with integer, otherwise you will loose decimal separator alignment.