Ask Your Question

summing fractions return wrong value [closed]

asked 2019-12-05 14:28:40 +0100

HerCorns gravatar image

I have noticed this strange behaviour in the =sum() function in calc.

image description

D1 contains the =sum(a1:c1) formula, as indicated in the screenshot.

Is sum only adding integer values and ignore float values?

Any suggestions on how this can be rectified?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by HerCorns
close date 2019-12-05 15:35:03.905698


Format D1!

ebot gravatar imageebot ( 2019-12-05 14:31:42 +0100 )edit

Format it to 6.0? The summed answer should be 8.5, so the fact that I did not format it correctly doesn't change a thing.

HerCorns gravatar imageHerCorns ( 2019-12-05 14:42:28 +0100 )edit

Some issues vanish next to immediately if their cause gets obvious.
Therefore: Avoid explicit horizontal alignment for cells wherever possible (which means next to always).
The horizontal 'Default' alignment works by displaying texts left aligned and numbers right aligned. Using this clever setting you will never again puzzle about why something looking like a number isn't processed as one.
If you want to have some distance between cell grid lines and cell contents you can use the 'Padding' (left, right) settings under 'Borders' for your formatting.

Lupp gravatar imageLupp ( 2019-12-05 18:18:09 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-12-05 14:44:52 +0100

Opaque gravatar image

updated 2019-12-05 14:49:01 +0100


most probably your 2.5 is not a number but text (see decimal delimiter in your locale) and text is 0 when used in SUM(). Thus 2 options:

  • Change the value to 2,5 -- or --
  • Change your locale, which has a . as a decimal delimiter (e.g. English (USA)

PS: If comment from @ebot would apply, you'd get 8

edit flag offensive delete link more


  1. How do you apply D1! format?
  2. How do you get the red text and grey boxes?
HerCorns gravatar imageHerCorns ( 2019-12-05 14:48:33 +0100 )edit

Do you mean locale setting - if yes: Tools -> Options -> Language Settings -> Languages -> Dropdown: Locale setting.

Opaque gravatar imageOpaque ( 2019-12-05 14:52:57 +0100 )edit

Add 1) Not at all - you entered 2.5 and obviously you are using a locale setting, which has , (comma) as a decimal delimiter. Thus LibreOffice doesn't recognize the data inserted as a number and stored it as text. BTW: That's why it is a bad idea to change the justification of your cells to center-alignment. If LibreOffice recognizes a number it get automatically right aligned and if an input is considered tect by LibreOffice it gets left aligned. So you'd have a direct indication how LibreOffice interpreted your values (very important for dates and times, since these are also numbers in LibreOffice).

Add 2) See the small icon in edit mode showing 101010 and help text preformatted text when hovering over the icon.

Opaque gravatar imageOpaque ( 2019-12-05 15:04:35 +0100 )edit

Thank you very much.

HerCorns gravatar imageHerCorns ( 2019-12-05 15:31:49 +0100 )edit

See also this FAQ.

erAck gravatar imageerAck ( 2019-12-05 16:08:15 +0100 )edit

Question Tools

1 follower


Asked: 2019-12-05 14:28:40 +0100

Seen: 122 times

Last updated: Dec 05 '19