Re: [libreoffice-users] Formatting in LO spreadsheet

At 19:42 28/06/2014 +0200, Rob Jasper wrote:
> I have a calc spreadsheet (.ods) ...
> So I select the columns and change the formatting to #.##0,00#
> This works as expected. Above values are still 
> displayed as before, but values 0,125 (for 12 
> and a half cents) are displayed with 3 digits.
> Here is an example before save/reopen:
> [...]
> 2       1852    ƒ       0,10    0,005
> 4       1864    ƒ       0,125   0,05
>
> If I now save and close the spreadsheet, and 
> open it again all values are all of a sudden 
> displayed with 3 digits behind the decimal 
> comma! This is what it becomes when I open the file:
>
> 2       1852    ƒ       0,100   0,005
> 4       1864    ƒ       0,125   0,050
>
> Is this bad behavior of LO calc or am I missing something here?

What you don't tell us is what the formatting 
looks like after you reopen the document. I 
suspect it will have changed from #.##0,00# to 
#.##0,000 . In other words, Calc is accepting 
your original formatting and faithfully 
displaying the values as you wish, but is not 
managing to preserve this requirement in the saved document.

The definition of ODF says that the format code 
is "a sequence of characters with an 
implementation-defined meaning", so clearly it 
cannot be saved in the document file exactly as 
you enter and see it. A bit of experimentation 
suggests that instead, an explanation of the 
format is included in the "styles" element, and 
this allows only such things as "decimal-places", 
"grouping" (whether you want the thousands 
separator), "currency-symbol", 
"min-exponent-digits", and so on. This would 
suggest that your desired format, although acted 
upon by Calc, indeed cannot be saved in an .ods 
file and is lost when you attempt this.

The only remaining odd thing is that the help 
text suggests that "#.0#" will display "13 as 
13.0 and 1234.567 as 1234.57" - which is exactly 
the functionality you require. Whilst this is 
true in the application, it's evidently not 
possible to preserve it in a saved document.

An obvious workaround is to create text values in 
a new column, using =TEXT(Xn;"#.##0,00#") - which 
you can then right-align if you prefer. You can 
continue to use the actual numerical values in 
calculations, whilst hiding that column if you 
prefer. If you need to enter values in the 
numerical column, an alternative technique would 
be to have another area - perhaps on another 
sheet - where a copy with properly formatted 
values was created. You could then print just that area or sheet.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted