Bug 144896 - problem with SUMIF, SUBTOTAL and AUTO FILTER leaving trace numbers when total should be 0
Summary: problem with SUMIF, SUBTOTAL and AUTO FILTER leaving trace numbers when total...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.1.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-10-03 15:53 UTC by Kristine
Modified: 2021-10-28 21:40 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
screenshot (109.83 KB, application/vnd.oasis.opendocument.text)
2021-10-03 15:53 UTC, Kristine 		Details
Sample ods file (28.86 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-04 14:51 UTC, Kristine 		Details
SUMIF result on CO360 (167.53 KB, application/vnd.oasis.opendocument.text)
2021-10-04 14:52 UTC, Kristine 		Details
example of problem (18.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-27 19:16 UTC, Kristine 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Kristine 2021-10-03 15:53:31 UTC 
Created attachment 175480 [details]
screenshot

When I add a column of numbers the SUM is correct, but if I use a filter, SUBTOTAL or SUMIF on that same column of numbers there are traces of numbers left about 12 decimal places to the right.  It's hard to explain, but I have attached a sample.
Comment 1 m_a_riosv 2021-10-04 07:55:45 UTC 
Attachment it's an image, not a sample file. Please attach the ods file.
Comment 2 Kristine 2021-10-04 14:51:20 UTC 
Created attachment 175507 [details]
Sample ods file
Comment 3 Kristine 2021-10-04 14:52:03 UTC 
Created attachment 175508 [details]
SUMIF result on CO360
Comment 4 QA Administrators 2021-10-05 04:19:47 UTC Comment hidden (obsolete)
Comment 5 Kristine 2021-10-27 19:16:06 UTC 
Created attachment 175962 [details]
example of problem
Comment 6 Michael Warner 2021-10-28 12:16:25 UTC 
Maintaining precision to a large number of decimal places is impossible due to the way computers store floating point numbers. Excel has the same issue, see:

https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

If you don't truly need precision to 20 decimal places, then I recommend you either change the cell formatting to reduce the number displayed, or use ROUND in your calculations.