Created attachment 109088 [details] Spreadsheet sample and a screenshot of my laptop just in case it works on your computer Problem description: In some cases value 1 is not correctly calculated. Please see attached files for explanation. I am attaching ods file as well as a picture. The reason is that I tried it in MS Excel and it shows the same problem as well. So I am not sure whether the problem is not somewhere in windows or in my machine rather than in Calc. I tried it in OpenOffice 3.1.1 on RedHat 5 and it shows the same issue. The other thing is that it is really difficult to spot this issue since you need to change the type to number with 20 decimal places. If you have fewer decimal places the system round it to 1. But the rounding is not done internally since Trunc function gives you 0 instead of 1. Current behavior: equation =INT(8.45)+(8.45-INT(8.45))/0.6-7.75 gives 0.99999999999999800000 the same for Floor function =FLOOR(8.45,1)+(8.45-FLOOR(8.45,1))/0.6-7.75 gives 0.99999999999999800000 Expected behavior: equation =INT(8.45)+(8.45-INT(8.45))/0.6-7.75 equal 1 Trunc of the line above gives 1
(In reply to Jindrich Svorc from comment #0) > Created attachment 109088 [details] > Spreadsheet sample and a screenshot of my laptop just in case it works on > your computer > > Problem description: > In some cases value 1 is not correctly calculated. > > Please see attached files for explanation. I am attaching ods file as well > as a picture. The reason is that I tried it in MS Excel and it shows the > same problem as well. So I am not sure whether the problem is not somewhere > in windows or in my machine rather than in Calc. > I tried it in OpenOffice 3.1.1 on RedHat 5 and it shows the same issue. I think this is just a matter of floating-point arithmetic. > Current behavior: > equation > =INT(8.45)+(8.45-INT(8.45))/0.6-7.75 gives 0.99999999999999800000 > the same for Floor function > =FLOOR(8.45,1)+(8.45-FLOOR(8.45,1))/0.6-7.75 gives 0.99999999999999800000 Note that there are 14 9's and 15 non-zero places in the number: 0.999 999 999 999 998 000 00 If we compare that result with a simpler calculation such as =1/3 we find a similar situation with only 15 decimal places displaying the expected result values: 0.333 333 333 333 333 000 00 So it appears that while LibreOffice may format a number to 20 places, the internal calculations are only valid up to 15 places.
I'm tempted to mark this as a dupe of Bug 67026 - Other: Limitations in Calc precision need to be documented, but I'm going to check with one of the Calc devs first. Perhaps the 15/20 behavior is unintended. Also see: Bug 37923 - Improve Calc precision when subtracting large integers to parity with Excel.
(In reply to Robinson Tryon (qubit) from comment #1) > (In reply to Jindrich Svorc from comment #0) > > Created attachment 109088 [details] > > Spreadsheet sample and a screenshot of my laptop just in case it works on > > your computer > > > > Problem description: > > In some cases value 1 is not correctly calculated. > > > > Please see attached files for explanation. I am attaching ods file as well > > as a picture. The reason is that I tried it in MS Excel and it shows the > > same problem as well. So I am not sure whether the problem is not somewhere > > in windows or in my machine rather than in Calc. > > I tried it in OpenOffice 3.1.1 on RedHat 5 and it shows the same issue. > > I think this is just a matter of floating-point arithmetic. > > > Current behavior: > > equation > > =INT(8.45)+(8.45-INT(8.45))/0.6-7.75 gives 0.99999999999999800000 > > the same for Floor function > > =FLOOR(8.45,1)+(8.45-FLOOR(8.45,1))/0.6-7.75 gives 0.99999999999999800000 > > Note that there are 14 9's and 15 non-zero places in the number: > 0.999 999 999 999 998 000 00 > > If we compare that result with a simpler calculation such as =1/3 we find a > similar situation with only 15 decimal places displaying the expected result > values: > 0.333 333 333 333 333 000 00 I agree with 1/3 but 8+(8.45-8)/0.6-7.75 should give exact 1, not 0.9999999999... 1/3 is periodically ended number but the result of the equation above is not. If you calculate it by hand you will get 1, not something close to 1. That's what puzzles me. Interestingly, if you change the equation to get result = 2 it calculates it correctly. I would not care too much since I usually don't need the 15th place but the problem is that INT function gives me 0 instead of 1 so it suddenly creates a big difference. > > So it appears that while LibreOffice may format a number to 20 places, the > internal calculations are only valid up to 15 places.
(In reply to Jindrich Svorc from comment #3) > > I agree with 1/3 but 8+(8.45-8)/0.6-7.75 should give exact 1, not > 0.9999999999... > 1/3 is periodically ended number but the result of the equation above is > not. If you calculate it by hand you will get 1, not something close to 1. > That's what puzzles me. Sure, but you aren't representing 0.45 or 0.6 as floating-point numbers :-) > Interestingly, if you change the equation to get result = 2 it calculates it > correctly. Indeed! Look at this: (8.45-8)/0.6 -> 0.749 999 999 999 999 000 00 0.45/0.6 -> 0.750 000 000 000 000 000 00 (7.45-7)/0.6 -> 0.750 000 000 000 000 000 00 (3.45-3)/0.6 -> 0.749 999 999 999 999 000 00 (9.45-9)/0.6 -> 0.749 999 999 999 999 000 00 > I would not care too much since I usually don't need the 15th place but the > problem is that INT function gives me 0 instead of 1 so it suddenly creates > a big difference. Perhaps you could use 10 or 15 places of precision? That should hopefully round the number up or down as expected...
(In reply to Robinson Tryon (qubit) from comment #4) > (In reply to Jindrich Svorc from comment #3) > Sure, but you aren't representing 0.45 or 0.6 as floating-point numbers :-) Right, ok, that explains it. Thank you. I thought the floating number should give you exact result. No additional rounding needed. But apparently it is not valid in all cases. It might make sense to round it for N-1 bits. Just one additional comment, It was not visible anywhere that the number in the cell is actually 0.999999999980 and not 1. I am not sure whether it is deliberately but it was difficult to find the root-cause. Even the sum in the status bar showed 1. Maybe if the INT function round the number for 14 decimal places before it is applied it can help. But I am not sure whether I can foresee all the consequences. Thanks for explanation. > > > Interestingly, if you change the equation to get result = 2 it calculates it > > correctly. > > Indeed! Look at this: > (8.45-8)/0.6 -> 0.749 999 999 999 999 000 00 > > 0.45/0.6 -> 0.750 000 000 000 000 000 00 > (7.45-7)/0.6 -> 0.750 000 000 000 000 000 00 > > (3.45-3)/0.6 -> 0.749 999 999 999 999 000 00 > (9.45-9)/0.6 -> 0.749 999 999 999 999 000 00 > > > I would not care too much since I usually don't need the 15th place but the > > problem is that INT function gives me 0 instead of 1 so it suddenly creates > > a big difference. > > Perhaps you could use 10 or 15 places of precision? That should hopefully > round the number up or down as expected...
*** This bug has been marked as a duplicate of bug 67026 ***
just to set some things clear: the intial fail for this bug isn't any function like INT, MOD or FLOOR ... but the representation of originally 'decimal' defined values by dyadic / binary 'floating-point' numbers with limited length, and the subtraction of such values 'with similar magnitude', e.g. '8,45 - 8' on such operations the 'upper bits' are zeroed by 'cancellation', and only few significant bits are left to define the result, (consider metering a distance to 1,052 +/-0,0005 meters, that's about +/- 0,5 promille accuracy, now subtract 1 meter with similar precision, you have 0,052 meter with a potential error of +/- 0,001 m, that's about 2 percent error range), thus the result has as much 'missing accuracy' as the magnitude of the result is below the magnitude of the arguments, because the absolute error of up to +/- 0,5 ULP, which is inherent in any fp-value, is kept, but is much bigger relative to a smaller result than the bigger operand before, and thus becomes visible, (it becomes more than one ULP of the result by 'bit shifting' to gain 'normalized' representations with a '1' in the first place), 8,45 is in the range where fp's are puzzeled together from '1,x * 2^3', while 0,45 as result is in a range where they are constructed from 1,y * 2^-2, thus an error of up to 0,5*2^-49 injected from 8,45 (where it vanishes in the lack of precision / granularity of values) is now affecting a number ~2^5 times smaller, and that is! visible ... and if not instantly spotted or rounded away for display (column to small or numberformat insufficient) it becomes visible by 'flip-flop' ('borderline') functions like INT, MOD, FLOOR etc. which don't calculate smooth 'near target', but make digital decisions 'in' or 'out', german proverb: 'just beside it is also over' as long as calc doesn't implement 'decimal-safe' datatypes or starts to consider the (decimal) string representation for the calculations ... the only chance for users is to try to avoid such calculations ... the developers try their best and achieve small improvements every now and then, but the basic problem remains and cannot be solved 'within' the simple IEEE 754 standard from 1985, '1/3 is periodically ended number but the result of the equation above is not' - the result shouldn't be, but it suffers from intermediate values like 8,45 being endless fractions in binary, and from the 'error blow up' by 'high-bit-cancellation', to get 0,45 you need 01101 in the end of the mantissa, but the mantissa doesn't know and pulls 5 zeroes from the 'off', 'So it appears that while LibreOffice may format a number to 20 places' - the 20 decimals are 'fake' in most cases, for values like 1,222... you get 14, for 0,345... you get 15 valid decimal places and 6/5 padding '0', 0,00000123... can use the whole range of digits, 'the internal calculations are only valid up to 15 places.' - 64 bit fp allows 15,95 decimal digit accuracy, cutted down to 15 by calc, of which some sometimes get lost in rounding, scientific format shows max 14 digits but has at least one more digit internal (you can key it in, but cannot see ... )