Bug 138595 - not a [calc ROUND() precision bug] but: calculation with day-time-fractions imprecise as 8 hours are represented by 1/3, thus 0.3333333333333333 with a small roundoff error, (and other fp-imprecisions?)
Summary: not a [calc ROUND() precision bug] but: calculation with day-time-fractions i...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.3.1 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-12-01 12:20 UTC by beckyomegakawaii
Modified: 2020-12-08 11:52 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
ods example of bug (31.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-07 11:16 UTC, beckyomegakawaii
Details

Note You need to log in before you can comment on or make changes to this bug.
Description beckyomegakawaii 2020-12-01 12:20:13 UTC
Description:
Video displaying the bug
https://youtu.be/Qg3eWC104c4

ODF file in question
https://mega.nz/file/WyYhHCTC#XxMHXwCSk6p3a_BtMFo5lr1C8IvelwhvMn8mtyNyb7w

Steps to Reproduce:
1.Have a formula that results in a number with EXACTLY .5000000000~ (not 0.499999~ or similar)
2.ROUND() the result
3.Most of the time rounds up as it should, but sometimes it rounds down.

Actual Results:
Rounds down

Expected Results:
Rounds up


Reproducible: Sometimes


User Profile Reset: No



Additional Info:
This has been happening since a long time ago, but I thought it was my machine/the formula etc. I have confirmed that it happens on every single computer i've ever tried.
Comment 1 Uwe Auer 2020-12-01 16:56:44 UTC Comment hidden (obsolete)
Comment 2 Uwe Auer 2020-12-01 16:57:46 UTC
(In reply to Uwe Auer from comment #1)
> Change all formulas in row 38 to values besides cell F38 and the rounding is
> correct to 3.794.00 in cell T38. From my perspective accumulation of
> rounding errors.

Sorry - correction:

Change all formulas in row 38 to values besides cell **T38** and the rounding is correct to 3.794.00 in cell T38. From my perspective accumulation of rounding errors.
Comment 3 ajlittoz 2020-12-01 17:11:34 UTC
Additional info: see discussion at https://ask.libreoffice.org/en/question/280060/massive-round-bug/
Comment 4 b. 2020-12-01 19:42:45 UTC
there is more than one issue here: 

1.) a file to download from an unknown provider is - no good - pls. upload to 'bugs', 'add an attachement', 

2.) a hectic fuzzy video is not a good analysis aid, 

3.) calcs 'round' functions (inc. rounddown, roundup, roundsig) indeed have some small flaws, @erAck is working on it, see tdf#138220, tdf#138424, and tdf#138860, 

4.) calcs calculations are not 'decimal safe' in themself, sometimes small inaccuracies occur, sometimes they add up, sometimes they have catastrophic blow-up's, sometimes they cause 'borderline fails', sometimes they become visible to the user, there are some proposals for enhancement, wait if / what will come, 

5.) users should be aware of 4.), there are some aids for correct work despite limited precision, mainly 'round' and 'precision as shown', 

(that the original decision for 'IEEE floats' was made regarding 'performance', and that all the benefit is lost in all this rounding users have to care for, and doubled lost in the time they loose investigating wrong results and 'learning floating point math' is ... suboptimal), 

6.) users also have to be aware that the results shown or printed by calc are mostly not! the values calc calculates with, but something rough 'prettyfied' for human acceptance, e.g. most numbers are shrinked to the size of the cell, all are limited to 15 decimal digits, if applied to the format given by the user ... but they contain additional digits, thus 'exactly 0,50000~' may be '0,4999~7' where the user needs special skills to access the hidden digits, 

7.) if there is additional fail in the OP's case we'd like to investigate further acc. additional explanation and the file, pls. provide and upload, 

setting 'needinfo',
Comment 5 beckyomegakawaii 2020-12-04 13:54:25 UTC
Hello, sorry for the late reply. Here's an explanation.

C38, Employee earns THB51 per hour, and has worked for 56 hours. Results in EXACTLY 2856.00
N38, Employee earns 1.5 times the normal working hour for overtime,(51*1.5=76.5). Has worked for 13 hours. Results in EXACTLY 994.50
Q38, Total employee earning, 2856.00+994.50 = EXACTLY 3850.50
R38, 57.00 subtracted from total earnings
S38, Net earning, is 3850.50 minus 57.00. Results in EXACTLY 3793.50

I don't see anywhere that any rounding errors could accumulate?
Comment 6 b. 2020-12-04 18:00:13 UTC
that's calculateable ... here with ver 7.1.0.0.a1, 

pls. uplaod the defect file ...
Comment 7 beckyomegakawaii 2020-12-07 11:16:29 UTC
Created attachment 167896 [details]
ods example of bug
Comment 8 QA Administrators 2020-12-08 04:26:41 UTC Comment hidden (obsolete)
Comment 9 b. 2020-12-08 11:52:53 UTC
ok, the problem (your error) has at least three stages: 

1.) the values you propagated as 'exact numbers' to be calculated with are not 'exact' numbers but in themself computed from other numbers (as it already is explained in 'ask'), 

2.) numbers you see on screen are - if computed - not! (mostly not) an exact decimal representation of what the computer is calculating with, but something 'prettyfied' for better acceptance by humans, 

3.) somebody sometimes decided to calculate time values in calc (as in ex$el and in 1-2-3? before) in a manner that '1' represents one day, and thus 8 hours are represented by a third (8/24) of 1, '0,333333333333~', a number already difficult for decimal calculations, becoming even more difficult with limited count of digits and not! becoming 'better' in conversion to binary, thus each of your seven 'eights' accumulated contains a litte roundoff error of about a 3 in the 17th position, that's hidden from you as a user to avoid some irritations, but produces others as you see, 

in detail your calculation in cell C38 is '=1/3*7*408/8*24', shown to you as '2856', but for the computer internally about '-0,00000000000045474735' 'off', and thus something with 2855,9999999999995452...', what causes irritations in rounding and downstream calculations, 

you can check it with '=RAWSUBTRACT(1/3*7*408/8*24;2856)'

ways to avoid: 

- calculate with 'real' numbers for hours instead of 'time formatted day fractions', '=8*7*408/8' shouldn't have such problems, 

- let the people work 6 hours a day, 6/24 has less problems than 8/24, 

- round your results at well selected steps to well selected precision, '=ROUND((1/3*7)*408/8*24;2)' - you expect no 100'th of TB? - would hold, 

setting 'notabug', hope you agree,