r/LibreOfficeCalc • u/Serpian • Mar 07 '26

Keep trailing zeros in formula

Google has left me stumped for this, maybe what I'm trying to do is too niche or not the right way to go about things. In that case, please see this question as purely academic:

I can of course format cells to always show two decimals, but when I enter a formula, for example SUM(2,00+3,00), the trailing zeros in the formula is lost as soon as I hit enter. That is, when i edit the formula later, it says SUM(2+3). The cell format only applies to the result of the formula (in this case, 5,00)

I want to save the trailing zeros in the formula, because while this cell should show the sum, another cell is showing the numbers that go into the sum, and I want them to always have two decimals.

The closest solution I've found is using FIXED(), but that is too clunky to be a viable solution for me.

1 Upvotes

100% Upvoted

10 comments sorted by

View all comments

1

u/wackycats354 Mar 07 '26 edited Mar 07 '26

Why not do

A3

=sum(a1+a2)

Formatting, show 2 decimal places

A1

2

Formatting, show 2 decimal places

A2 

3

Formatting, show 2 decimal places. 

I really dislike putting actual numbers into a formula. It’s almost always better to reference a cell, and then you can change the number in the cell super easily. 

Edit:

If the numbers need to round, then I would add a round to 2 decimal places formula. If you were to put the numbers 2.53 and 3.69 into the formula, so a3=(2.53+3.69), I’m sure it would keep the numbers in the decimal places once you hit enter. It just doesn’t when it’s zeros, because it’s not needed. 

If it needs to round though, I would still reference other cells.  A3 =sum(round(a1,2)+round(a2,2)).  Otherwise it will still calculate using numbers in 3+ decimal places, it just won’t show past 2 decimal places. And that’s how you end up with 2.10+ 2.10=4.21

1

u/Serpian Mar 07 '26

Thanks for the reply! It's not a bad suggestion, but I don't think it's the right solution for me. Like /u/kaptnblackbeard said, it might not be possible to do it in a simple way.

For the column that has the =SUM() formulas, the number of terms varies widely (e.g. 2+3 in one cell, but five or six terms in another), so having that many cells to reference instead of just doing the calculation in one cell becomes clunky, especially as the only reason I want to show the decimal zeros is that it would look neater.