Precision problem with OOo 1.1 Calc?!

PSIplus · Guest

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

SergeM · Posted: Mon Mar 22, 2004 10:19 am Post subject:

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

PSIplus · Guest

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!

nom · 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

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

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

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

Guest

erikanderson3 · 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. Crying or Very sad

I'll keep my eyes peeled Shocked

for any other news.

Cheers,

Erik

erikanderson3 · 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,

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 · 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

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

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.

erikanderson3 · 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 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...

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

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.

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