Re: [libreoffice-users] Re: LOcalc shows an inaccurate output when use cells autofill function

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.

-- 
Jay Lozier
jslozier@gmail.com

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
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