Not calculating SUM correctly

Discuss the spreadsheet application

Not calculating SUM correctly

Postby jrwalter » Mon Dec 16, 2019 9:15 pm

Hi all,

I'm trying to calculate the sum of 10 cells of values. They are all numbers, not text, and all set to AutoCalculate. I have also copied and pasted the values into a new column and retyped the same formula and still getting the wrong sum.

The sum should be $9021.28 but it keeps showing up at $9021.32. I have triple checked that each value is correct. I tried setting all the cells to ROUNDDOWN but that's not working and I'm wondering if that's what's tripping up the sum.

I'm a complete beginner at this so any help is much appreciated.
OpenOffice 4.1.3
OS Windows 10
jrwalter
 
Posts: 1
Joined: Mon Dec 16, 2019 9:02 pm

Re: Not calculating SUM correctly

Postby RoryOF » Mon Dec 16, 2019 9:20 pm

See this reply on another list for an explanation and a solution
https://ask.libreoffice.org/en/question/221559/sum-of-columns-not-correct/
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 30435
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Not calculating SUM correctly

Postby John_Ha » Tue Dec 17, 2019 2:00 am

See below where I set B2, B3 and B4 to be equal to 1/3.

When I format the cell to display only two digits after the decimal I see 0.33 displayed. You are assuming this means that the actual value is 0.330000000000000000...

But the actual value held is 0.3333333333333333333333333... which is 0.0033333333333333333333333... bigger. When I add a lot of values all the little 0.3333333333333333333333333... get added as well and this is what causes the apparent difference.

The actual sum Calc calculates $9021.32 is correct. The value $9028.28 you get by manually adding the digits you see displayed is wrong.

If you want both values to be equal you must truncate each individual value to the accuracy you require (eg to 0.333 if you require three digits of accuracy) and display the answer to equal or greater resolution. It will now be 0.999000000000000000000000...

Clipboard02.gif
Attachments
sum.ods
(8.53 KiB) Downloaded 3 times
LO 6.3.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7269
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests