Incorrect SUM value unless "Precision as shown" is checked

Discuss the spreadsheet application

Re: Incorrect SUM value unless "Precision as shown" is check

Postby RoryOF » Sat May 09, 2015 7:20 am

Set the display accuracy on the destination cell and read up on the mechanism of arithmetic on computers. You are the thinking machine, not the computer.
Apache OpenOffice 4.1.6 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27694
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Incorrect SUM value unless "Precision as shown" is check

Postby jrkrideau » Sat May 09, 2015 1:20 pm

It ain't broke. The numbers are the same, they just are formatted differently.

Go to the cell with the =SUM(xxx;yyy) in it, go to Format > Cells > Numbers and change the number of decimals to 15 and see what you get :) Or to your 4.92000000000007 cell and format it to 2 decimals and see what happens.

See Section 2 of Ten concepts that every Calc user should know

IIRC any number in Calc is stored to roughly 15 digits accuracy. This is a computer architecture issue and not a Calc or any other spreadsheet issue. If you need very high precision in your calculations there are some programs (Mathematica is one I believe) that will do this.

My thanks to RusselB for reminding me of the great If it aint broke, don't fix it saying.
AOO 4.1.5; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3583
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Incorrect SUM value unless "Precision as shown" is check

Postby coewar » Sat May 09, 2015 1:54 pm

..
Last edited by coewar on Sat May 09, 2015 2:12 pm, edited 1 time in total.
OpenOffice 4.1.1 Windows 7
coewar
 
Posts: 6
Joined: Sat May 09, 2015 12:37 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby coewar » Sat May 09, 2015 2:04 pm

What the heck? Grabbed a copy of Excel and same thing.

WOW


How can this be OK as a formatting issue? The problem why this came up is that other functions that use the resulting value FAIL TO WORK because of this. A simple function of =if( cell <> 0; "NOT ZERO"; "" ) fails to work.. I essentially can't use the condition of this.

Sorry for the post, but I mean... just shocking.

By the way, I even enabled the "precision as shown" and still got the same problem.. so I can't get around this unless I use =MROUND( cell; 0.01 ) in the formulas.
OpenOffice 4.1.1 Windows 7
coewar
 
Posts: 6
Joined: Sat May 09, 2015 12:37 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby acknak » Sat May 09, 2015 3:26 pm

coewar wrote:... =if( cell <> 0; "NOT ZERO"; "" ) fails to work. ...

Clearly this is a different problem. Calc has no trouble distinguishing non-zero values from zero.

Maybe one of the inputs to your condition is actually text, not a number, and Calc is taking the value as zero? Hard to say without looking at the sheet.

You can check value types with View > Value Highlighting: ON

I even enabled the "precision as shown" and still got the same problem.. so I can't get around this unless I use =MROUND( cell; 0.01 ) ...

Using MROUND is how I prefer to handle it because you have control over what cells are rounded and the rounding is "up front" not hidden in a setting.

But "precision as shown" works; however, it depends on the cell format. So if the cell contains the SUM formula, AND the cell format is set to two decimal places, Calc will automatically round the formula result from 4.92000000000007 to 4.92 exactly. It works for me.

I must say though that the result is surprising to me. It seems that Calc is able to represent both inputs (formatting to max precision shows all zeroes) but the SUM is off by more than one in the rightmost digit. Another floating point arithmetic surprise, I suppose.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Incorrect SUM value unless "Precision as shown" is check

Postby jrkrideau » Sat May 09, 2015 6:41 pm

acknak wrote:
coewar wrote:... =if( cell <> 0; "NOT ZERO"; "" ) fails to work. ...

Clearly this is a different problem. Calc has no trouble distinguishing non-zero values from zero.


I can replicate this. Well, I put 0 into a1 and b1 and did
=IF(A1<>B1; "Okay"; "Oops")
results were Oops.

This does not look good :?

gnumeric
=if(A1<>B2,"Okay","Oops")
Oops


I got the same results for a1 = 1 & a2 = 1

I tried the same thing in R and got the expected results
1> aa <- 0
1> bb <- 0
1> aa==bb
[1] TRUE

 Edit: =IF(A1=B1; "Okay"; "Oops")
Okay
in both AOO and gnumeric.
 


I would understand if 0.1^-20 != 0.11^-19 but 1 != 1 is strange.

It looks like we are still dealing with a floating point issue.

Does anyone have Excel or some other spreadsheet to run a test?

 Edit: Missed the point that coewar has already checked it out in Excel 
AOO 4.1.5; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3583
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Incorrect SUM value unless "Precision as shown" is check

Postby coewar » Sun May 10, 2015 1:25 am

Ya.. I tried the formula in Excel as well and same problem. Which is that Calc and Excel do not know the VALUES of the cells, despite any formatting.
No formatting solves the issue.

MROUND() method seems to be the only option.

It's still shocking.. :)
OpenOffice 4.1.1 Windows 7
coewar
 
Posts: 6
Joined: Sat May 09, 2015 12:37 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby jrkrideau » Sun May 10, 2015 3:08 am

Did you try A1 = B1 rather than A1 <> B1?

This seems to be the problem. Calc, Excel and gnumeric all seem to agree that "<>" != "=". In which case I am not sure that the problem is shocking since they probably are not exactly the same operation and <> is hitting the floating point issue.
AOO 4.1.5; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3583
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Incorrect SUM value unless "Precision as shown" is check

Postby coewar » Mon May 11, 2015 12:56 am

Well I specifically want to test that a value is NOT ZERO. :) So.. I can't exactly use "="

however, just to test.. I did try with '=' and same incorrect results. =IF(E6=4.92;"it is";"no it aint")
OpenOffice 4.1.1 Windows 7
coewar
 
Posts: 6
Joined: Sat May 09, 2015 12:37 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby jrkrideau » Mon May 11, 2015 4:59 am

Weird
=if(a1 = 4.6; "okay"; 'Oops")
returns ""okay"" where a1 = 4.6
Presumably, we have some different settings or formats
AOO 4.1.5; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3583
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Incorrect SUM value unless "Precision as shown" is check

Postby acknak » Mon May 11, 2015 2:25 pm

Sorry, I must have lost the thread somehow. I thought this was resolved--what's the issue?

Here's what I get:
arithmetic_limit.png


So the arithmetic produces a value that's different from what you see on the screen. That's normal. You have to deal with it: either ROUND the value or use "precision as shown" and set the cell format to two decimal places.

Is there something else going on here?
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Incorrect SUM value unless "Precision as shown" is check

Postby coewar » Sun May 17, 2015 3:24 am

I don't know why, but on all versions of Open Office I have (3 and 4) the "Precision as shown" makes no difference.
It's just an option for the entire app, not at cell or spreadsheet level.. and I tried creating a new document with the setting on and it still shows the inaccuracy.

But further, I don't always want precision as shown.

I re-started this because I did not realize this was an issue outside of Open Office.
OpenOffice 4.1.1 Windows 7
coewar
 
Posts: 6
Joined: Sat May 09, 2015 12:37 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby jrkrideau » Sun May 17, 2015 5:51 am

coewar wrote:I don't know why, but on all versions of Open Office I have (3 and 4) the "Precision as shown" makes no difference.
It's just an option for the entire app, not at cell or spreadsheet level.. and I tried creating a new document with the setting on and it still shows the inaccuracy.

But further, I don't always want precision as shown.

I re-started this because I did not realize this was an issue outside of Open Office.


It is something one needs to be aware of in a spreadsheet but the basic problem is the nature of computer calculations. It is a common enough question in the R statistics environment that the issue has its own FAQ. 7.31 Why doesn’t R think these numbers are equal?
The examples there might help. http://cran.r-project.org/doc/manuals/R ... equal_003f. And the quote from “The Elements of Programming Style” by Kernighan and Plauger:
10.0 times 0.1 is hardly ever 1.0.
rather sums up the issue.

If you require extreme precision in your numbers I believe there are programs such as Mathematica or Maple that can provide it but normal computer programs are not going to.

II suspect Calc and Excel may have slightly different rounding conventions which may lead to discrepancies between the two spreadsheet programs.
AOO 4.1.5; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3583
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

[Solved, sorta]Re: Incorrect SUM value ...

Postby ChasVA » Sat Dec 30, 2017 6:56 am

I've created a simple ledger for my bank account.

All numbers are entered with no more than two decimals.

I've created a conditional formatting style to highlight every third row (much like old computer paper) for ease of reading.

In the last column to the right of the calculated ledger, I enter the bank balance shown online from my bank. If that amount matches the ledge balance, both numbers display in blue bold type, again via a conditional formatting style.

This worked seamlessly until row 38 of the attached sheet, when suddenly Calc calculated 112.51 - 31.26 as 81.2499999999996, rather than 81.25 (which is what displays). Now the conditional formatting doesn't work, because the 81.25 that I enter in the far right column doesn't equal the 81.2499999999996 Calc provided as the answer to a simple subtraction problem.

I discovered what Calc's result was by copying the cell containing the result into another cell using Paste Special and choosing "Numbers". By clicking on the target cell and looking at the Formula Bar, I discovered the number with all the decimal places.

If I turn "Precision as shown" ON, everything works again, but why should I have to? I don't understand how 112.51 - 31.26 ever equals anything other than 81.25. I could understand if some or any of the numbers involved in the calculation had multiple decimals, but they're all numbers with two decimal places.

Any help?

Image

Precision Error.ods
There are notes on the issue in a text box on the sheet.
(19.64 KiB) Downloaded 44 times
Last edited by ChasVA on Sun Dec 31, 2017 2:35 am, edited 2 times in total.
Version: OpenOffice 4.1.5
OS: Win 8.1 64-bit

Never try to second-guess the clueless. Malcolm Gordon
User avatar
ChasVA
 
Posts: 16
Joined: Tue May 31, 2016 11:14 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby Villeroy » Sat Dec 30, 2017 7:33 am

The c.f. is AND(MOD(ROW();3)=1; $F38=$G38) which does not match since MOD(ROW();3) returns 2 in row 38
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25984
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Incorrect SUM value unless "Precision as shown" is check

Postby FJCC » Sat Dec 30, 2017 8:52 am

Also, concerning the precision of calculations in Calc, all computer calculations are done in binary (base 2). Numbers that are exactly represented in decimal notation may not be exactly represented in binary. You have run into such a case. The same thing happens in decimal notation trying to represent the fraction 1/3, it cannot be done exactly.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6893
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Incorrect SUM value unless "Precision as shown" is check

Postby ChasVA » Sat Dec 30, 2017 10:57 am

Villeroy wrote:The c.f. is AND(MOD(ROW();3)=1; $F38=$G38) which does not match since MOD(ROW();3) returns 2 in row 38


This has nothing to do with the conditional formatting; the value of the number returned by the calculation is incorrect. I'll address your issue with the conditional formatting, however:

Image

Note that there are three conditions in the conditional formatting.

  1. If cell F38 falls on a row where MOD(ROW();3)=1 and its value is equal to the value of G38. then its background is shaded and its contents are displayed in bold blue type.
  2. If it doesn't fall on a row where MOD(ROW();3)=1 and its value is equal to the value of G38. then its background is unshaded and its contents are displayed in bold blue type.
  3. If it only falls on a row where MOD(ROW();3)=1 then only its background is shaded.
To show that the formatting works, look at cells F35 and G35 for an example where the values match and MOD(ROW();3)=1 also returns 2, just as it does for row 38.
Version: OpenOffice 4.1.5
OS: Win 8.1 64-bit

Never try to second-guess the clueless. Malcolm Gordon
User avatar
ChasVA
 
Posts: 16
Joined: Tue May 31, 2016 11:14 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby Villeroy » Sat Dec 30, 2017 1:26 pm

Sorry, how could I miss that? Yes, this is the inevitable floating point conversion problem and there is nothing to do about that except rounding, option "precision as shown" or using an appropriate tool for accounting which stores and compares decimal numbers of a fixed type instead of boiling everything down to floats.
https://support.microsoft.com/en-us/hel ... s-in-excel

c.f. condition formula #2
ROUND($F38;2)=ROUND($G38;2)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25984
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Incorrect SUM value unless "Precision as shown" is check

Postby soby » Sat Dec 30, 2017 1:44 pm

Sorry i have copy F38 to G38 with copy special and have become 81.25 and so the conditional formatting is working for me
"Precision as shown" is off
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
User avatar
soby
Volunteer
 
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby Villeroy » Sat Dec 30, 2017 2:10 pm

soby wrote:Sorry i have copy F38 to G38 with copy special and have become 81.25 and so the conditional formatting is working for me
"Precision as shown" is off

LO 6 beta or AOO 4 ?
I see the same problem in LO 5 and AOO 4.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25984
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Incorrect SUM value unless "Precision as shown" is check

Postby soby » Sat Dec 30, 2017 2:19 pm

LO6.1.0alpha and AOO 4.1.5

I have to say sorry Villeroy your right and i am wrong problem is also with LO
Last edited by soby on Sun Dec 31, 2017 4:57 pm, edited 3 times in total.
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
User avatar
soby
Volunteer
 
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby acknak » Sat Dec 30, 2017 7:39 pm

I think others have already given the main points, so I just have a couple of further hints:

1) You can view the exact, unformatted result of a formula by pressing F2, then F9. At that point, Enter will enter the result in the cell (in edit mode) and Enter again will replace the formula with the result. For D38, this gives 81.2499999999996. Saves a bit of fiddling to get the actual value out.

2) The heart of the problem is almost certainly the conversion to binary, but that's not the only thing at work. The formula =112.51-0+31.26 produces 81.25, with no problem evident. What makes the problem appear in your sheet is that the balance calculations do not happen in isolation: each one depends on all the previous balance calculations. So, even though each one may appear to be fine, errors may accumulate, depending on the exact values in the sheet.

I don't know what the best way to handle this is, at least since calc doesn't support any sort of alternate calculation method beyond "precision as shown".

One alternative would be to change the formulas to explicitly round the result: =IF(OR(D38>0; E38>0); ROUND(F37+D38-E38;2); "")
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Incorrect SUM value unless "Precision as shown" is check

Postby ChasVA » Sat Dec 30, 2017 10:05 pm

soby wrote:Sorry i have copy F38 to G38 with copy special and have become 81.25 and so the conditional formatting is working for me
"Precision as shown" is off


That's not the way I use the spreadsheet. I don't copy the calculated balance in column F and use paste special to put it in column G; I enter the value in column G and it should match. I suspect if you click in G38 and look at the formula bar that you'll see the same erroneous value with the long decimal places.
Version: OpenOffice 4.1.5
OS: Win 8.1 64-bit

Never try to second-guess the clueless. Malcolm Gordon
User avatar
ChasVA
 
Posts: 16
Joined: Tue May 31, 2016 11:14 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby ChasVA » Sat Dec 30, 2017 11:10 pm

acknak wrote:I think others have already given the main points, so I just have a couple of further hints:

1) You can view the exact, unformatted result of a formula by pressing F2, then F9. At that point, Enter will enter the result in the cell (in edit mode) and Enter again will replace the formula with the result. For D38, this gives 81.2499999999996. Saves a bit of fiddling to get the actual value out.

2) The heart of the problem is almost certainly the conversion to binary, but that's not the only thing at work. The formula =112.51-0+31.26 produces 81.25, with no problem evident. What makes the problem appear in your sheet is that the balance calculations do not happen in isolation: each one depends on all the previous balance calculations. So, even though each one may appear to be fine, errors may accumulate, depending on the exact values in the sheet.

I don't know what the best way to handle this is, at least since calc doesn't support any sort of alternate calculation method beyond "precision as shown".

One alternative would be to change the formulas to explicitly round the result: =IF(OR(D38>0; E38>0); ROUND(F37+D38-E38;2); "")


Thanks for the F2 / F9 method explanation—that's handy, for sure.

While I'm aware that there are formulaic solutions to the problem, I strongly feel this is a bug. This issue doesn't arise in Excel using these numbers and formulas, so somehow they're handling the cumulative imprecision differently. (I didn't have to turn Excel's "Precision as displayed" option on, either.) Additionally, I'm a fairly competent spreadsheet user who knew at least a bit about how to ferret out the problem. Someone less well-versed in spreadsheets isn't going to understand why their spreadsheet says two numbers that look to be identical on screen aren't, in fact, equal to each other.

And it's not always going to be apparent, either. If you change the value in cell E27 to 26.25 (and change all the values in column G to match the new calculations in column F), the error shows up on line 37, even though the absolute value of F37 matches G37 exactly—there are no stray decimal points when copying and using Paste Special to enter just the number in H37, or when using the F2/F9 method to examine the calculation. I added the formula =ABS(F#) to column H to display the values in F with 20 decimal places for comparison and to identify where the failure occurs. There's no reason for the conditional formatting to fail in cell G37.

Image

This is a checkbook register, for pity's sake—possibly one of the least-complicated spreadsheets in existence, and probably one that's used a great deal. Take away all the fancy conditional formatting I've added (because I know how) and you're left with extremely basic calculations. Without the formatting highlighting the error, to the naked eye all looks well. But how long before the error creep compounds to the point where the value in column F is just flat-out wrong?

I'll fix this using one of the methods proposed and mark this as solved, but I strongly feel this should be reported as a bug to the developers. This error showed up after a mere 34 calculations. And since that isn't the case in Excel, there's obviously a different way to handle it in the coding. Perhaps Excel builds in some sort of rounding? Whatever's happening in Calc, it's happening beyond 20 decimal places (which is the maximum number of decimal places that can be displayed). If it's up to me to report the bug, please explain how to do that.

Many thanks to you and everyone else who took the time to respond. Have good days!
Version: OpenOffice 4.1.5
OS: Win 8.1 64-bit

Never try to second-guess the clueless. Malcolm Gordon
User avatar
ChasVA
 
Posts: 16
Joined: Tue May 31, 2016 11:14 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby robleyd » Sun Dec 31, 2017 12:11 am

If you wish to report a bug or request an enhancement: [Tutorial] Reporting bugs or suggestions
Cheers
David
Apache OpenOffice 4.1.6 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2265
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Incorrect SUM value unless "Precision as shown" is check

Postby Lupp » Sun Dec 31, 2017 12:50 am

This is clearly not about a bug but about a well known consequence of the fundamental limitations of arithmetic. The basic fact was already mentioned by "acknak".

Let's calculate a simple exercise four ways: 1/3 + 1/3 + 1/3

Calculation with ordinary fractions: 1/3 + 1/3 + 1/3 = 3/3 = 1
Calculation with decimal fractions limited to 4 decimal places: 0.3333 + 0.3333 + 0.3333 = 0.9999 <> 1
Calculation with dyadic fractions limited to 10 dyadic places: 0.0101010101 + 0.0101010101 + 0.0101010101 = 0.1111111111 <> 1
(Result converted to decimal: 1 - 2^-10 = 0.9990234375)
Calculation in the base-12-system: 0.4 + 0.4 + 0.4 = 1 (exact; no conversion or rounding errors)

There is no system allowing exact calculation with rational numbers in every case except a rational arithmetic of fractions without any limitations concerning the length of the representation (though the representations always are finite).

Editing
It's annoying to get offered images only in a case of such a subtle issue instead of a real spreadsheet document showing the problem. There are so many ramifications probably to check for.
OK. I wasted half an hour to get something similar to what "ChasVA" already had.
After some experimenting I would support the claim that there is a bug. Though we have to admit the accumulation of rounding/conversion errors as a fact, some alghebraic identities should not fade out first due to the limitations of arithmetic, but have arbitrary revivals if the order of magnitude of the values concerned decreased.

Anybody may use the attached demo for a bug report. The behaviour is the same still even in LibO V 6.0.0.0Beta2.
Attachments
aoo3713_Comparison_1.ods
(47.38 KiB) Downloaded 20 times
Last edited by Lupp on Sun Dec 31, 2017 2:26 am, edited 2 times in total.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2187
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Incorrect SUM value unless "Precision as shown" is check

Postby acknak » Sun Dec 31, 2017 1:06 am

It's clear that there are some strange "features" deep in the numeric minutia of Calc. I'm not qualified to say whether any of them are bugs. Feel free to report it, but don't expect to get any traction on it. Getting a different result from Excel is not going to seal the argument: Calc doesn't claim to be an Excel clone and (I believe) specifically says that differences in handling calculations at the extremes of precision should be expected. Caveat programmer.

Here's a simple spreadsheet that demonstrates the problem (several, actually). Column A has a chain sum: each cell adds a constant onto the previous value. Column B calculates the same values by multiplying the constant by an integer. Column C compares the values using the = operator. Column D compares the values by converting them to text with fifteen digits after the decimal, then comparing the text with EXACT.

You can see that the accumulated error appears fairly quickly, after about sixty rows. You can also see that the two comparisons don't always agree. And the F2/F9 trick also seems to give yet a different result in some rows.

The only answer I know of to this problem is to be aware of it and to protect your results by rounding when doing long-chain calculations of any kind.
Attachments
calc_precision_compare_test.ods
(36.6 KiB) Downloaded 22 times
Last edited by acknak on Sun Dec 31, 2017 1:27 am, edited 1 time in total.
Reason: Updated attahment
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Incorrect SUM value unless "Precision as shown" is check

Postby ChasVA » Sun Dec 31, 2017 2:32 am

Lupp wrote:It's annoying to get offered images only in a case of such a subtle issue instead of a real spreadsheet document showing the problem.

In case you missed it, I attached a spreadsheet to my original post. The images in my last post were to document the changes I made in that attachment so that others could easily duplicate my results by making those same changes. In future, I suppose I'll attach the edited spreadsheet.
Version: OpenOffice 4.1.5
OS: Win 8.1 64-bit

Never try to second-guess the clueless. Malcolm Gordon
User avatar
ChasVA
 
Posts: 16
Joined: Tue May 31, 2016 11:14 am

Re: Incorrect SUM value unless "Precision as shown" is check

Postby Lupp » Sun Dec 31, 2017 3:14 am

Yes, I missed that. Sorry! My apologies!
You may have a look into the sheet I created and attached to my previous post. It actually contains some "research" concerning the problem how to delimit the occurrence of "false positives" on comparison for equality. Is there a reasonable explanation? I am interested in opinions on the demo. (I also consider a bug report to the DocumentFoundation.)
You were interested in what you thought to be "false negatives". Regarding this original issue I have nothing new: Explicit rounding should be the means of choice.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2187
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Incorrect SUM value unless "Precision as shown" is check

Postby C J Ham » Tue Jan 02, 2018 9:27 pm

This post is closing on 10 years old and the problem posed in the original question seems to have gotten lost in the discussion of the calculations mysteriously becoming "corrupted" by unwanted decimal values starting at about 10 or 11 places. The problem seems to be industry wide, as I personally have encountered this non-bug bug using older versions of Excel, as well as current versions of both Excel & OO Calc.

The meat of the original question, as I understood it, was; 'Why aren't the programs being set to use "Precision as Shown" by default upon installation?'
It would seem the world at large would benefit from that being the standard or at least an option offered during installation.
I would wager the general populace may not even be aware that their large complex spreadsheets are being thrown off by cumulative errors.
:crazy:
OpenOffice 4.1.4 on Windows 7
Confusion is the state of trying to process information that is either incomplete, inaccurate or more complex than you comprehended.
Life is often just trying to figure out which condition is causing the confusion.
C J Ham
 
Posts: 1
Joined: Tue Jan 02, 2018 9:02 pm

PreviousNext

Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 32 guests