Formatting in LO spreadsheet

users
#1

Hi all,

maybe I am just not doing things right, but I have a weird problem.

I have a calc spreadsheet (.ods) and I have 2 (F&G in the following example) columns which represent a Dutch Florin/Euro value. Therefore I want the value to show at least 1 digit, and 2 digits behind the decimal point (that is a , in the Netherlands). So, the value shows like 0,05 (5 cents); 0,10 (10 cents), 2,15 (2 guilders 15 cents) etc.

So, set the formatting to #.##0,00; So far so good.

However... In the earlier days NL had half cents. So, I want to keep the formatting the same, but extend with one digit if needed do denote half cents...

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:

Stat VEIL-ID NVPH nr Jaar
Nom. Waarde Toeslag Serie AlbumBlad Album
g
1 1852 ƒ 0,05
1 1
.
2 1852 ƒ 0,10 0,005
1 1
.
3 1852 ƒ 0,15 0,50 3 1 1
.
4 1864 ƒ 0,125 0,05
1 1

If I now save and close the spreadsheet, and open it again all values are all over sudden displayed with 3 digits behind the decimal comma!
This is what it becomes when I open the file:

Stat VEIL-ID NVPH nr Jaar
Nom. Waarde Toeslag Serie AlbumBlad Album
g
1 1852 ƒ 0,050
1 1
.
2 1852 ƒ 0,100 0,005
1 1
.
3 1852 ƒ 0,150 0,500 3 1 1
.
4 1864 ƒ 0,125 0,050
1 1

Is this bad behavior of LO calc or am I missing something here?

Any advice appreciated!

Rob.

#2

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

#3

Brian,

thank you for your answer.

Indeed when I look at the formatting after saving and reopening the file, the formatting changed from #0,00# to #0,000 .

All predefined number formats are saved, and restored upon reopening. If I define a user-defined format, it is all over sudden not saved...
Also, if I save in MS .xlsx format it comes up fine in both MS-Excel (Excel for Mac 2011, V14.0.0 (100825)). If I open that file with LO it has also my defined formatting still available.

Regardless what the technical cause is for this, it is at least user unfriendly??
Questions to be asked:
- What use has a user defined number format, if it can't be saved?
- If this is indeed a restriction in the ODF definition, why is LO not warning like "The defined format can not be saved in the desired file format"?
- Why does LO consider the format change a change in the first place? (If I open the file, change the format as I like it, it is considered changed, while the file stays exactly the same)
- Should we consider this as a flaw in de ODF definitions? Where can we complain about this?

So, in my opinion LO behaves inconsequent at least!

Best,

Rob.

#4

Indeed when I look at the formatting after saving and reopening the file, the formatting changed from #0,00# to #0,000 .

All predefined number formats are saved, and restored upon reopening. If I define a user-defined format, it is all of a sudden not saved...

No, that's not true: it's just this particular - and rather unusual - format with a hash after the zeroes (rather than before) that evidently cannot be saved. Note that such formats are apparently *never* saved as such in ODF files - just a description in a different form that indicates the same format, but which is not capable of describing the unusual format that you have chosen.

Also, if I save in MS .xlsx format it comes up fine in both MS-Excel (Excel for Mac 2011, V14.0.0 (100825)). If I open that file with LO it has also my defined formatting still available.

I'm guessing, then, that the actual format character sequence is saved in that file format.

Regardless what the technical cause is for this, it is at least user unfriendly?

Possibly.

Questions to be asked:
- What use has a user defined number format, if it can't be saved?

Come, come: user-defined formats generally *can* be saved, just not all of them - and apparently not your rather unusual one. Perhaps the designers of Star Office / OpenOffice / LibreOffice based the format code on Microsoft's, knowing that it could saved in Microsoft's document formats? Could it perhaps be saved in the old Star Office .sxc format?

- If this is indeed a restriction in the ODF definition, why is LO not warning like "The defined format can not be saved in the desired file format"?

Dunno.

- Why does LO consider the format change a change in the first place? (If I open the file, change the format as I like it, it is considered changed, while the file stays exactly the same)

Any change is a change, including a format change. You wouldn't change the format if you didn't want that to change something. This situation is rather as if you replaced some character in a document with an identical character: the document is still considered changed. Indeed, there may be unobvious ways in which it actually will be.

- Should we consider this as a flaw in the ODF definitions?

That's a value judgement for you to make. It's certainly something that can be handled in Calc but apparently not saved in an ODF document.

Where can we complain about this?

Either to OASIS (if you want the ODF format modified to allow this) or to the LibreOffice bug reporting system (if you want your original format not to work even at first, or if you want a warning that it cannot be saved in ODF documents).

Brian Barker

#5

Hi Brian,

I implemented the next workaround-

Set the default formatting of the column to 0,000
Create conditional formatting to be 0,00 with the following formula: MOD(C2;0,01)=0

This is doing the job!

Best,

Rob.