On Thu, 2011-10-06 at 11:30 +1300, Steve Edmonds wrote: > > On 2011-10-06 10:52, planas wrote: > > Hi > > > > On Wed, 2011-10-05 at 11:15 -0700, Pedro wrote: > > > > > >> Juan Carlos wrote: > >> > >>> I have done the same thing with google docs and it did it with no errors. > >>> > >>> > >> Actually, Google just cheats :) > >> It only allows 10 decimal cases. > >> > >> Since the first error in Excel at 4.0 requires 14 decimal cases to be shown > >> it will never show up in Google Docs. > >> > >> Do this calculation in Google Docs and you can test for yourself that it is > >> using Base 2 calculations as well > >> > >> 4095414.77 - 4095398.34 = > >> > >> Sorry about the bad news... > >> > >> -- > >> View this message in context: > >> http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397411.html > >> Sent from the Users mailing list archive at Nabble.com. > >> > >> > > This is not just a spreadsheet issue but related to the precision of the > > floating point numbers with computers. Basically the more bytes used for > > the number the better the precision. A general rule it is easier to get > > more precision with 64 bit computers than 32 bit computers, however > > software may limit the precision for compatibility reasons to the > > equivalent of 32 bit. > > > > A related issue, not a computer only problem, occurs when you subtract > > two numbers the precision of the answer drops drastically. In the above > > example the answer is 16.43. If the two numbers were measurements, the > > significant figures state that each number is only accurate to about +/- > > 0.02 and when subtracted the error is basically additive. Division is > > another area were you can get some wacky precision effects especially > > when dividing a small number with a large number. This is called > > propagation of error. The calculations that are done on real data the > > worse the error is in the final answer, this is true if you did the > > calculations with a pen and paper, calculator, or computer. Calculating > > the propagation can be very tedious. Often the problem of the underlying > > precision of the data is more significant than the computer's precision, > > but it is not always true. To fully understand the effects, one should > > do some propagation of error based on the data and on the computer's > > precision. > > > > If you saw some number in a spreadsheet for the above that was slightly > > different than 16.43 it is fundamentally due to the precision of all > > floating arithmetic on computers. The spreadsheet may make worse by > > rounding the floating precision to a lower one than the computer can > > handle. When I did the calculation on my cell phone I got > > 16.4300000000168 and using Calc 16.4300000002. > > > > If you want a more detailed discussion on precision problems, both from > > the data and from the computer, consult a numerical methods text. > > > Thats interesting. In Calc I get 16.4300000001676 and in Kcalc I get > 16.43 (to 20 decimal places) >
That is not surprising, the exact rounding errors will vary in Kspread I get 16.4300000002. What is interesting is I have only seen positive errors reported (actual > true), with enough random data it should be 50/50 positive to negative. -- Jay Lozier [email protected] -- For unsubscribe instructions e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
