View previous topic :: View next topic 
Author 
Message 
PSIplus Guest

Posted: Mon Mar 22, 2004 7:18 am Post subject: Precision problem with OOo 1.1 Calc?! 


Hi!
I use OpenOffice.org 1.1.0 in mathematics in school, and I have a heavy problem:
NORMINV(0,975;0;1)
Should output something like 1.959962, but the result in OOo is 1,960395!!
That's bad!
I depend on a working spreadsheet application for my math test... any ideas?
Have Fun!
Martin 

Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France


Back to top 


PSIplus Guest

Posted: Mon Mar 22, 2004 11:45 am Post subject: 


I'm sure about it... You know, in math lession the teachers want this precision... (TI92 and 200 and even excel and lotus 123 report the correct value...)
But thanks for the hint... I'll report it! 

Back to top 


nom OOo Enthusiast
Joined: 17 Dec 2003 Posts: 153 Location: NSW, Australia

Posted: Mon Mar 22, 2004 4:45 pm Post subject: 


From what I can gather, OOo calc does have an accuracy problem (There is a bounty to solve this problem http://www.gnome.org/bounties/OpenOffice.org.php3). Not that it really matters for most of us but if you want to do high end statistics etc... you would probably want to double check your answers.
I believe the best spreadsheet program for statistics and financial analysis is Gnumeric. Excel used to (and I think is still does) have some accuracy problems as well....
You may want to read this:

Abstract: The open source spreadsheet package "Gnumeric" was such a good clone of Microsoft Excel that it even had errors in its statistical functions similar to those in Excel's statistical functions. When apprised of the errors in v1.0.4, the developers of Gnumeric indicated that they would try to fix the errors. Indeed, Gnumeric v1.1.2, has largely fixed its flaws, while Microsoft has not fixed its errors through many successive versions. Persons who desire to use a spreadsheet package to perform statistical analyses are advised to use Gnumeric rather than Excel.
http://www.csdassn.org/software_reports.html

Would be interesting if they compared OOo clalc as well.
It is worth reporting your problem in anycase. Let us know what the issue number is and I will vote for it.
nom 

Back to top 


Colin Davies Newbie
Joined: 23 Mar 2004 Posts: 3 Location: South Africa

Posted: Tue Mar 23, 2004 6:44 am Post subject: precision in open office calc 


I see some people are using calc for stats. As a land surveyor I have a similar problem in doing trig math where 16 decimals are required to get the right answers.
This is particularly a problem when dealing with large numbers which are multiplied.
While I know there is a problem, is there any idea of when a solution may be found?
Regards
Colin _________________ Colin BollaertDavies
colin@npmgeomatics.co.za 

Back to top 


erikanderson3 OOo Advocate
Joined: 25 Feb 2004 Posts: 332 Location: San Francisco peninsula

Posted: Tue Mar 23, 2004 6:53 am Post subject: Re: precision in open office calc 


Colin Davies wrote:  is there any idea of when a solution may be found? 
Have a look here: http://qa.openoffice.org/issues/show_bug.cgi?id=22811
The patches appear to be set for the 2.0 release, which (if I understand the timeline correctly) will probably be released sometime this summer. As for whether the patches supply the kind of precision you need, I do not know, but you can probably tell yourself from the developers' discussion on the linked page.
HTH,
Erik 

Back to top 


Guest

Posted: Tue Mar 23, 2004 10:07 am Post subject: Re: precision in open office calc 


Colin Davies wrote:  I see some people are using calc for stats. As a land surveyor I have a similar problem in doing trig math where 16 decimals are required to get the right answers.
This is particularly a problem when dealing with large numbers which are multiplied.
While I know there is a problem, is there any idea of when a solution may be found?
Regards
Colin 
Hi,
Just curious. Why 16 places? What units of measure are you using? What sort of absolute error would you expect (feet, miles, meters, kilometers) in a land survey if out by 1 in the 16th decimal place? I'd not worry to that extent if calculating a distance on the moon. The percentage error might be in the order of 10^(13), and even when two figures are multiplied, that error is in the same order, since percentage errors are added.
Thanks.
David. 

Back to top 


erikanderson3 OOo Advocate
Joined: 25 Feb 2004 Posts: 332 Location: San Francisco peninsula

Posted: Thu Mar 25, 2004 9:24 pm Post subject: 


Update on 2.0 
I may have misinterpreted the timeline; what's out this summer might just be another developer's release.
I'll keep my eyes peeled for any other news.
Cheers,
Erik 

Back to top 


erikanderson3 OOo Advocate
Joined: 25 Feb 2004 Posts: 332 Location: San Francisco peninsula

Posted: Mon Apr 19, 2004 7:28 pm Post subject: Re: precision in open office calc 


Two things 
First, have a look here for information on the OOo release schedule. According to this, the 2.0 final release is scheduled for the end of the year, with an alpha out in the summer and a beta in the fall.
Second, David wrote:  Just curious. Why 16 places? What units of measure are you using? What sort of absolute error would you expect (feet, miles, meters, kilometers) in a land survey if out by 1 in the 16th decimal place? I'd not worry to that extent if calculating a distance on the moon. The percentage error might be in the order of 10^(13), and even when two figures are multiplied, that error is in the same order, since percentage errors are added. 
The NORMINV example above shows a deviation from the expected figure beginning at the second decimal place. Even if you don't need 16place accuracy (and I can imagine places where you would need something that heavy duty), it would sure be nice if OOo could get at least the 6place accuracy needed for the NORMINV example. I suspect the upcoming 2.0 release might well offer that.
Cheers,
Erik 

Back to top 


Guest

Posted: Tue Apr 20, 2004 7:57 am Post subject: Re: precision in open office calc 


Not trying to stir up a can of worms, but I'm still left curious of the need for 16 decimal places in any sort of surveying. Around here, they are accurate to 1/1000 of a foot or 1/1000 of a degree, and that's more than enough.
David.
erikanderson3 wrote:  Two things 
First, have a look here for information on the OOo release schedule. According to this, the 2.0 final release is scheduled for the end of the year, with an alpha out in the summer and a beta in the fall.
Second, David wrote:  Just curious. Why 16 places? What units of measure are you using? What sort of absolute error would you expect (feet, miles, meters, kilometers) in a land survey if out by 1 in the 16th decimal place? I'd not worry to that extent if calculating a distance on the moon. The percentage error might be in the order of 10^(13), and even when two figures are multiplied, that error is in the same order, since percentage errors are added. 
The NORMINV example above shows a deviation from the expected figure beginning at the second decimal place. Even if you don't need 16place accuracy (and I can imagine places where you would need something that heavy duty), it would sure be nice if OOo could get at least the 6place accuracy needed for the NORMINV example. I suspect the upcoming 2.0 release might well offer that.
Cheers,
Erik 


Back to top 


erikanderson3 OOo Advocate
Joined: 25 Feb 2004 Posts: 332 Location: San Francisco peninsula

Posted: Tue Apr 20, 2004 5:31 pm Post subject: 


I don't know at thing about surveying really, I was thinking about other areas like stats and higher math, where long series of calculations can compound all those little precision errors into something noticeably larger.
Any surveyors out there care to bite?
Cheers,
Erik 

Back to top 


Doug J General User
Joined: 13 Apr 2004 Posts: 10

Posted: Wed Apr 21, 2004 1:21 am Post subject: 


The need for high precision in surveying is in doing things like calculating the intersection point of two nearly parallel lines, but the 14 figures available in all the widely used spreadsheets is plenty for all normal surveying purposes.
The question in this thread is a separate issue. Apparently the accuracy of the algorithm used in this particular function is inadequate. Nothing to do with the underlying precision of the spreadsheet. 

Back to top 


erikanderson3 OOo Advocate
Joined: 25 Feb 2004 Posts: 332 Location: San Francisco peninsula

Posted: Wed Apr 21, 2004 6:18 pm Post subject: 


Doug 
If you take a look at the posted bug report above (and here at http://qa.openoffice.org/issues/show_bug.cgi?id=22811), you'll note that there actually are some potentially serious issues with a good chunk of Calc's calculations . On the upside, these issues look set for resolution in 2.0 if not sooner, as the developers already seem to have a patch (mostly?) worked out . NORMINV just happens to be one of the spreadsheet functions that uses the faulty calculation methods.
If you're interested, you can get some funny numbers of your own with some very simple calculations. Open a spreadsheet, and in one column type 81 and 891. In the next three cells, have A1*A2, A2*A3, and A3*A4. You should get 72171, 64304361, and 4640910037731. In the next cell, multiply the three previous (A3*A4*A5), and in the last cell, enter A6/1.0E11. Make sure the final cell does not appear in scientific notation (requiring at least one decimal place is one way to do this).
In the next column, we have a similar progression starting from 8.10E+021and 8.91E+022. Keep all the formulae the same, except in the very last cell use B6/1.0E211.
Now comes the wierdness. You'll note that the two values are slightly different  one ends in a 3, and the other in a 4. There are no rounding issues, as showing decimal places indicates there's not enough to round to make a difference (nothing but zeroes five places out). But if you then subtract one from the other, you get 0.
Bear in mind I've done nothing fancier here than basic arithmetic  multiplying, dividing, and subtracting. Yet the onscreen results are odd, at best, implying some intrinsic problem in OOo's handling of numbers. The bug report goes into greater detail in this regard. Happily, this is on its way to being fixed. And just to be sure this particular oddness is looked into, I added it on as an addendum to the bug report.
Cheers,
Erik
Update:
Additional oddness. Despite the lack of decimal figures in the abovementioned results, on a lark I thought to look into things a bit further, and found the following:
A7  215380459783120 = 3.5000000
B7  215380459783120 = 3.6562500
Hmm... I would have expected 3.0000000 and 4.0000000... 

Back to top 


Doug J General User
Joined: 13 Apr 2004 Posts: 10

Posted: Wed Apr 21, 2004 8:04 pm Post subject: 


Erik
Thanks for your response. I'll have a look at the bug report when I have time.
I tried your example in OOo Calc (1.1.1), Excel 2000, and 123 R9.5.
With all three I got 215380459783124.00 displayed in row 7 for both coloums. A7B7 gave me 0 in Calc and Excel and 0.125 in 123.
Entering A7215380459783120, B7215380459783120 and A8B8 gave me:
Calc: 3.56, 3.66, 0.094
Excel: 3.5625, 3..6875, 0.1250
123: 3.5625, 3.6875, 0.1250
So all three gave very similar results.
I'd say that this was not a bug, rather a consequence of the way computers convert binary numbers to decimals. If you are working at the limit of the computer's precision (ie 15 decimal digits), then the last digit is not reliable.
If you need more than 15 didgits precision in a spreadsheet then the only options I know of are 123 for DOS (Rel 3 onwards) and, I think, Gnumeric.
This is not to say that calc does not have any problems in mathematical precision, but this example does not seem to be one of them. 

Back to top 


erikanderson3 OOo Advocate
Joined: 25 Feb 2004 Posts: 332 Location: San Francisco peninsula

Posted: Wed Apr 21, 2004 9:14 pm Post subject: 


Doug 
Hmm, I just tried in Excel 2000 too, but unlike your go, I got identical results to what I found in Calc (also 1.1.1).
Doug wrote:  This is not to say that calc does not have any problems in mathematical precision, but this example does not seem to be one of them. 
Not trying to be argumentative at all , but if Gnumeric can work to 15digit precision, should we not also expect OOo to do the same? Though I understand the failure of floatingpoint number systems, I don't have the technical ability to know how difficult Gnumericlevel precision is. I guess what I'm wondering here is how much inaccuracy do we need to consider it a 'bug'?
This begins to stray into philosophical waters. Ultimately, though, I suppose the point is moot in that the OOo patches are on their way.
Cheers,
Erik 

Back to top 




You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group
