Re: [libreoffice-users] Error or what?

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