Ask Your Question

How to make Calc remember decimals?

asked 2020-07-11 10:42:47 +0200

mattia.b89 gravatar image

updated 2020-08-07 23:14:36 +0200

Alex Kemp gravatar image

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?
(I think the other side of the coin)
How to make input line saving decimals entered?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-07-11 11:35:23 +0200

Lupp gravatar image

updated 2020-07-11 11:47:44 +0200

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!

edit flag offensive delete link more


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!

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-11 11:46:02 +0200 )edit

@Mike Kaganski: 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 ...

Lupp gravatar imageLupp ( 2020-07-11 12:08:08 +0200 )edit


  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?
Mike Kaganski gravatar imageMike Kaganski ( 2020-07-11 12:24:25 +0200 )edit

@Mike Kaganski: 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 ...(more)

Lupp gravatar imageLupp ( 2020-07-11 13:00:56 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-07-11 13:03:32 +0200 )edit

answered 2020-07-11 12:26:02 +0200

dscheikey gravatar image

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


edit flag offensive delete link more


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

Lupp gravatar imageLupp ( 2020-07-11 13:06:13 +0200 )edit

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.

dscheikey gravatar imagedscheikey ( 2020-07-11 13:31:24 +0200 )edit

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.)

Lupp gravatar imageLupp ( 2020-07-11 13:49:25 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2020-07-11 10:42:47 +0200

Seen: 42 times

Last updated: Jul 11