How to make Calc remember decimals?

[mattia.b89]

I’d like to have Calc cell values with decimal IF I type them.
I am not talking about format options.

I have to make an example to better explain the issue:
I type “1.00” in a cell and by default it shows “1” but if I change format, cell shows decimals too 1.00.
This feature has a drawback: decimals shown are decided by cell format and if I type “1” in the same cell, it shows 1.00 (or more zeros depending upon format’s precision).
This behaviour leads in a wrong way causing discordance between

• digits really typed (the input line has value 1)
• and digits displayed (cell shows 1.00).

How to make cells displaying decimals only if really entered?
or
(I think the other side of the coin)
How to make input line saving decimals entered?

[Lupp]

Even internally Calc doesn’t remember the way a number (may be information of the kind special number like dates representable by numbers) was once entered, or if it was entered via the keyboard or by pasting or…
While you are typing there is a recognition process guessing (kind of) whether you intend to enter a constant needing (by default) to be represented as a number - or if you want to enter text.
In addition there is a guess concerning the question if a ‘special number’ is input. In many cases the result of the guess depends on the locale and/or respective settings for the concerned cell. It will distinguish the as-if-types Date, Logical, Currency, Percentage, Scientific, and probably additional ones. The number type to which the entered sequence of key hits is converted after the final Enter is IEEE 754 Double in all these cases. The only trace the process of entering is leaving is the format automatically assigned to the cell then as a hard attribute. This is done by routines of the core code -and has effects Lupp (e.g.) clearly deprecates. He also cannot change this.

Depending on what, precisely, you actually want, there are thinkable workarounds coming with different disadvantages:
-W1- Enter your number explicitly as a text prefixing an apostrophe (or by setting the @ format code in advance).
-D1a- Such numbertexts may be automatically converted for calculations depending on the locale at least.
-D1b- These celle contents are ignored by accumulating functions like SUM(), AVARAGE(), and more.
-D1c- The handling and the consequences generally are error-prone as a result.
-W2- You create user code using a KeyListener to override the hardcoded default processes.
-D2a- A lot of studies and labor needed, also error-prone (depending on your skills).
-D2b- Will also need to use hard attributing (like te default processes in many cases).
-W3- You write user code working against unwanted effects after the fact (with onContentChanged e.g.) .
-D3- Similar as with -W2-
-W4- Something probably someone else will suggest …

My honest advice: Let it be!

[mikekaganski]

Additionally: there’s no reliable way to guess what format you intended to use. When you enter 1.00, was its intended format 0.00, or #.00, or #,###.00, or 0.00;[red]-0.00, or …?

Wrt hard formatting that is deprecated by @Lupp: yes it’s true that the direct formatting is not ideal. But otherwise, in the absence of dedicated date/time/boolean/percent/etc data type, there’s no way to make the entered “1%” or “2020-07-11” to be displayed reasonably … so some compromise is needed. But any constructive proposals are welcome (but of course would be considered wrt possible drawbacks, and naturally most would get rejected, which is a normal and expected result). Thanks @Lupp for all your work!

[Lupp]

@mikekaganski: There were many and still are mainly three effects I seriously deprecate:
-1- The locale-dependent default date formats cannot be changed and are against reason in most cases. This way LibO even encourages sticking to regional and ambiguous formats explicitly deprecated not only by Lupp, but also by ISO. In case of Italian(Italy) this also applies to time formats.
-2- A cell automatically hard attributed to Percentage due to once having gotten entered 105%(e.g.) will contain the text 103%% if you enter103% next time, and 1.03% if you try to enter a growth factor by typing 1.03 …
-3- In many aditional cases a once applied automatic hard NumberFormat will persist in cases where it’s obviusly against reason. Enter a currency value “recognized” by your locale e.g. Then enter into the same cell an ISO-8601-extended date …

[mikekaganski]

@Lupp:

1. This place is off-topic for this discussion, as you know.
2. Your words are not a constructive proposals, but just criticism.
3. LibreOffice is not a tool for enforcing users to use some (good) formats despite their local regulations. However good ISO format is, there are not many who made it a national standard (Canada - who else?). This doesn’t apply to other places (like date format used on this site, where I would support your criticism wholeheartedly), but this is also off-topic here in this question.
4. Since we have to use a direct formatting when deciding the format from entered value for the first time, the automatic format changes into some specific format. Then there’s no way to change a non-automatic format when user enters something the next time. Would you like the specific format you applied manually be replaced automatically next time you enter something?

[Lupp]

@mikekaganski: No “flamewar” intended! I would like to ensure you of my high appreciation.
Ad 3. I don’t know how, exactly, the situation is in Canda. From posts by a Canadian in a different site, however, I know that also there ISO is mostly disregarded - resulting in frequent misunderstandings. He is expressing criticism insofar. As a German living in Germany I can tell you that we have a DIN 5008 (national standard) prescribing ISO 8601 extended for business correspondence, and “allowing” DD.MM.YYYY only for letters assured to stay in Germany. Its’ ignored,mostly, of course.
Of course? Why? May standard software play its part in the game?
Prognosis now: Following the digitalisation hype on the one hand, and the localization madness on the other hand, we will end up within 10 years with a situation where we will unavoidably need the “services” of google to read our ordinary letters. A few additional years later nobody will learn a foreign language …
Cui bono?

[Lupp]

Ad 4. With Boolean format set due to having enterd TRUE or FALSE it’s different. A subsequently enterd 17 will show as the number.17 in General format.

[dscheikey]

Hello mattia b89,
I think the easiest way to achieve your goal is to format the cells for your input as text in advance. Then exactly as many decimal places as you enter are displayed. You can still calculate this in many places, since Calc can interpret the text as a number. Only comparison operations or functions that definitely expect a value cannot be created with these cells. But here you can build the VALUE() function around it.

Best regards

Juergen

[Lupp]

What about SUM(), COUNT(), AVERAGE(), MAX() … ?

[dscheikey]

Hello Lupp, I would not say that it is optimal to want to count on texts. But in many cases (certainly not in all) it is possible. In your examples it is possible to enter the formula as an array formula.
Let’s wait and see if my suggestion was helpful for the questioner. Maybe he / she is not so much concerned with calculating with the numbers or texts.

[Lupp]

Just one thought in addition:
It may also occur that a user not aware of the implications will get wrong results (using simple statistical functions like AVERAGE() e.g.) but trust in them.
As I was told by contributors to a different forum there even are well known cases where this kind of error seriousl afflicted scientific publications.)