Re: [libreoffice-users] Error or what?

Thanks Brian!

I realize that it is a rounding error that adds the decimals.
It actually behaves similarily with "percision as shown"

But mystery 2 still stays, why does the function kill itself from line 50+ ?

The spreadsheets are enclosed in the postings on nabble, link below image.

Best
/erik
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 (Nilhe AB) 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.
>
> I trust this helps.
>
> 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