Re: [libreoffice-users] Error or what?
Hi all, Thanks for the tip! I hope this works: http://shared.59551.x6.nabble.com/Error-or-td2.html /erik Sigrid Carrera skrev 2014-02-03 09:58: Hi Erik, could you please upload your screenshots somewhere and give us the link? Attachments are automatically removed, so we don't see what you've sent. /Sigrid On 3 February 2014 06:21, Erik Erlandsson (Nilhe AB) erik.erlands...@nilhe.se wrote: Im seeing this odd behavior in a series. Around line 30 additional decimals are showing up What am I doing wrong? Enclosing both screen-shot and spreadsheet as it might look different in other installations. I'm using Version: 4.1.4.2 Build ID: 410m0(Build:2) in Linux debian 3.12-1-amd64 #1 SMP Debian 3.12.6-2 (2013-12-29) x86_64 GNU/Linux -- With kind regards Erik Erlandsson -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Error or what?
Hi, It behaves more or less the same in Excel 2003 in Win7. http://shared.59551.x6.nabble.com/Error-or-Same-thing-in-Excel-td3.html The formula is from a book on chaos, it describes a way to simulate a controlled chaotic sequence: take a number, a fraction between zero and one, and double it. Then drop the integer part, the part to the left of the decimal point. Then repeat the process. Since most numbers are irrational and unpredictable in their fine detail, the process will just produce an unpredictable sequence of numbers. I expected something unpredictable, but not this! :) /erik On 2014-02-03 21:48, marianne-x wrote: Interesting, if unsavoury, behaviour. I can confirm the identical results from /Curtains 7/, and LO 4.1.4.2. Line 28 marks the spot. On a second /Curtains 7/ machine, LO deficiencies do not permit installation of main-line LO, but the portable version, 4.0.6.2, behaves in the same way. Since LO is not allowed on that 2nd machine, routine work there is done with OO 3.3.0, and it, too, behaves identically on this test. Perhaps someone with access to M$ will try the experiment there, to see how universal this phenomenon is. trj On 3 February 2014 06:21, Erik Erlandsson (Nilhe AB) erik.erlands...@nilhe.se wrote: Im seeing this odd behavior in a series. Around line 30 additional decimals are showing up What am I doing wrong? Enclosing both screen-shot and spreadsheet as it might look different in other installations. I'm using Version: 4.1.4.2 Build ID: 410m0(Build:2) in Linux debian 3.12-1-amd64 #1 SMP Debian 3.12.6-2 (2013-12-29) x86_64 GNU/Linux -- With kind regards Erik Erlandsson -- To unsubscribe e-mail to: users+unsubscr...@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
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+unsubscr...@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
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+unsubscr...@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
[libreoffice-users] Error or what?
Im seeing this odd behavior in a series. Around line 30 additional decimals are showing up What am I doing wrong? Enclosing both screen-shot and spreadsheet as it might look different in other installations. I'm using Version: 4.1.4.2 Build ID: 410m0(Build:2) in Linux debian 3.12-1-amd64 #1 SMP Debian 3.12.6-2 (2013-12-29) x86_64 GNU/Linux -- With kind regards Erik Erlandsson -- To unsubscribe e-mail to: users+unsubscr...@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
