2014 Archives by date, by thread · List index

# 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 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
```