At 22:56 03/02/2014 +0100, Erik Erlandsson wrote:
On 2014-02-03 22:20, Brian Barker wrote:
At 06:21 03/02/2014 +0100, Erik Erlandsson wrote:
I'm seeing this "odd" behavior in a series. Around line 30
additional decimals are showing up What am I doing wrong?
At 20:29 03/02/2014 +0100, Erik Erlandsson wrote:
I hope this works: http://shared.59551.x6.nabble.com/Error-or-td2.html
I'm having to guess, as you've posted a screenshot but not the
spreadsheet itself. But your column appears to be calculated by an
iterative formula, with each row calculated from the previous
one. As you do this, rounding errors will be introduced, and these
will increase in size as you double the value at each step. Since
you take only the fractional part of the result, the number in each
row does not generally grow in size, so the rounding errors
eventually become significant compared with the values you have.
The calculation is probably being carried out internally to around
fifteen significant figures. When I try this calculation, rounding
errors appear at row 8, but only in the fifteenth significant figure.
This will be happening in your calculation too, but becomes visible
only in row 26, where the difference begins to show in the nine
significant figures you chose to display. Note that the rounding
will occur in the binary numbers being used in the calculation by
your computer hardware, not in the decimal values being
displayed. Your results all terminate after the first fractional
place in decimal, but they will not do so in binary, where even 0.1
is the recurring fraction 0.0[0011] with those last four digits repeating.
You will see different results - perhaps even those you seek - if
you tick Tools | Options... | LibreOffice Calc | Calculate |
Precision as shown. This causes the displayed value you see to be
used in each calculation instead of the true value hidden in the cell.
Of course, you could apply some mathematics and find a simpler way
of calculating these values - which, after the first, simply repeat
the sequence 0.2, 0.4, 0.8, and 0.6. One example is:
=MOD(2^(ROW()-1);10)/10
- but this will go awry at row 52, since 2^51 is so large (around
fifteen digits) that its units digit is no longer reliable.
So here's a better version:
=MOD(2^(MOD(ROW()-2;4)+1);10)/10
- which should work more or less indefinitely, though not for the
(exceptional) first row.
At 22:38 03/02/2014 +0100, Erik Erlandsson wrote:
It behaves more or less the same in Excel 2003 in Win7.
It will. You have called your spreadsheet document "Calcbug" - but
this is not a bug, but the inevitable consequence of using a finite
computing machine.
The formula is from a book on chaos, it describes a way to simulate
a controlled chaotic sequence: ...
It's perhaps unfortunate, then, that you misrepresented your original
query, suggesting that it was odd behaviour and asking what you were
doing wrong.
I expected something unpredictable, but not this!
It's all predictable if you understand what is happening under the
bonnet ("hood").
I realize that it is a rounding error that adds the decimals. It
actually behaves similarly with "precision as shown"
No, with "Precision as shown" the calculation proceeds differently
and straightforwardly, since the rounding errors are dropped at each
stage. But you may have to save and reopen the document file in
order to see the difference.
But mystery 2 still stays, why does the function kill itself from line 50+ ?
There's no mystery. The rounding errors become visible at your
chosen display precision at row 28, but by about row 52 they have
started to interfere with the single significant digit you are
attempting to calculate with - so even that becomes unstable. Once
the errors happen to cause a result to be zero, that special case
will be preserved without further error.
The spreadsheets are enclosed in the postings on nabble, link below image.
Sorry if I missed that earlier. Fortunately I guessed accurately
what you were doing.
Brian Barker
--
To unsubscribe e-mail to: users+unsubscribe@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
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.