Hilly Bill wrote:... and couldn't get it to misbehave like it was doing yesterday.
You surely changed the format - and it's easy to get a
lying format again.
Frankly: Formats are made for lying.
The inconsistency you complain about is a fact. How spreadsheets work nowadays was mostly defined by those who weren't much interested in quality and clear concepts, but in selling licenses. Free software is expected to be compatible. It must therefore implement te same misbehaviour.
Hilly Bill wrote:I'm going with a partly corrupted file that fixed itself when it was closed and re-opened. And hoping it doesn't happen again!
I don't think your document is actually corrupted.
To get an more detailed idea of the issue:
A complete number format code (without colours an other toys of the kind) may consist of up to four semicolon-separated parts. I wouldn't talk about the fourth part at the moment. The other three parts apply to
(1) all numbers if no second part exist, positive numbers only otherwise, and in additoion to 0 if no third part is present.
(2) negative numbers.
(3) only zero.
(A) The really confusing thing is that the number itself is taken including its sign for the first position, but a prefixed sign, if present, isn't treated as a mathematical operator, but like any arbitrary character.
(B) But for the second part the thing that gets formatted is the absolute amount of the result in the cell.
And ...
Basically it's simple: Follow the path of maximum confusion, and you may be right.
Once again. All that nonsense wasn't invented by or for OpenOffice.
(A) should be "implemented" on the request of extremely lazy users, trying to avoid the need of typing two doublequotes if constant text should be included with formatting.
(B) is most likely due to the generally low level of mathematical education. In fact it's again a bit demanding now and then to distinguish values from their amounts - specifically when talking about them.
Conclusions:
Never include mathematical symbols as literals in number formats.
Always designate literals in format strings with the help of doublequotes.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München