OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Precision problem with OOo 1.1 Calc?!
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
PSIplus
Guest





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

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! Smile

I depend on a working spreadsheet application for my math test... any ideas?

Have Fun!
Martin
Back to top
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Mon Mar 22, 2004 10:19 am    Post subject: Reply with quote

I am surprised you need a precision better than 0.1%. What kind of data do you have ?

This is not the first problem I see with statistical functions with OOo. You can perhaps post it as a bug if you are sure of the exact value.
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
PSIplus
Guest





PostPosted: Mon Mar 22, 2004 11:45 am    Post subject: Reply with quote

I'm sure about it... You know, in math lession the teachers want this precision... (TI-92 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
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Mon Mar 22, 2004 4:45 pm    Post subject: Reply with quote

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
View user's profile Send private message
Colin Davies
Newbie
Newbie


Joined: 23 Mar 2004
Posts: 3
Location: South Africa

PostPosted: Tue Mar 23, 2004 6:44 am    Post subject: precision in open office calc Reply with quote

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 Bollaert-Davies
colin@npmgeomatics.co.za
Back to top
View user's profile Send private message Send e-mail Visit poster's website
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Tue Mar 23, 2004 6:53 am    Post subject: Re: precision in open office calc Reply with quote

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
View user's profile Send private message
Guest






PostPosted: Tue Mar 23, 2004 10:07 am    Post subject: Re: precision in open office calc Reply with quote

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
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Thu Mar 25, 2004 9:24 pm    Post subject: Reply with quote

Update on 2.0 --

I may have misinterpreted the timeline; what's out this summer might just be another developer's release. Crying or Very sad

I'll keep my eyes peeled Shocked for any other news.

Cheers,

Erik
Back to top
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Mon Apr 19, 2004 7:28 pm    Post subject: Re: precision in open office calc Reply with quote

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 16-place 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 6-place accuracy needed for the NORMINV example. I suspect the upcoming 2.0 release might well offer that.

Cheers,

Erik
Back to top
View user's profile Send private message
Guest






PostPosted: Tue Apr 20, 2004 7:57 am    Post subject: Re: precision in open office calc Reply with quote

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 16-place 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 6-place 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
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Tue Apr 20, 2004 5:31 pm    Post subject: Reply with quote

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
View user's profile Send private message
Doug J
General User
General User


Joined: 13 Apr 2004
Posts: 10

PostPosted: Wed Apr 21, 2004 1:21 am    Post subject: Reply with quote

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
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Apr 21, 2004 6:18 pm    Post subject: Reply with quote

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 Sad. On the up-side, these issues look set for resolution in 2.0 if not sooner, as the developers already seem to have a patch (mostly?) worked out Smile. 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 on-screen 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 above-mentioned 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
View user's profile Send private message
Doug J
General User
General User


Joined: 13 Apr 2004
Posts: 10

PostPosted: Wed Apr 21, 2004 8:04 pm    Post subject: Reply with quote

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. A7-B7 gave me 0 in Calc and Excel and -0.125 in 123.

Entering A7-215380459783120, B7-215380459783120 and A8-B8 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
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Apr 21, 2004 9:14 pm    Post subject: Reply with quote

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). Question Question

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 Smile, but if Gnumeric can work to 15-digit precision, should we not also expect OOo to do the same? Though I understand the failure of floating-point number systems, I don't have the technical ability to know how difficult Gnumeric-level 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. Smile

Cheers,

Erik
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
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