Re: [libreoffice-users] Error or what?

Hi Brian,

Initially I thought it was a bug, but now I understand more or less what 
is happening in both "mysteries"
Still, ticking "Precision as shown" does no difference at all to the 
result even after closing and reopening.

I rest my case, thank you for the education!

/e


On 2014-02-04 06:30, Brian Barker wrote:
> 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