Re: [libreoffice-users] Re: LOcalc shows an inaccurate output when use cells autofill function
Any plans for python functions? - from twohot@device.mobile :) -Original Message- From: Andreas Säger ville...@t-online.de Date: Fri, 07 Oct 2011 07:50:30 To: users@global.libreoffice.org Reply-To: users@global.libreoffice.org Subject: [libreoffice-users] Re: LOcalc shows an inaccurate output when use cells autofill function Am 07.10.2011 00:00, JOE Conner wrote: I think I understand. You are essentially saying that OOo has functions in the standard library that LO does not - correct? Joe No, all I say is that the user defined functions are in the wrong library. The code can not be found which raises #NAME errors because of the unknown function names. After moving the module to Standard and reloading the document, all the cells are calculated, albeit very, very slowly because Basic is inappropriate for this type of cell functions. -- For unsubscribe instructions e-mail to: users+h...@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 -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 06/10/11 17:53, JOE CONNER wrote: http://nabble.documentfoundation.org/file/n3398776/XNumbers_01.ods XNumbers_01.ods -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3398776.html Sent from the Users mailing list archive at Nabble.com. I get a lot of #NAME? in 3.3.4, should the functions work, macros are enabled. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Same here. Doesn't work under LO 3.4.3 or OOo 3.4 Beta The Excel version available from the linked site does work... under Excel :) (The results are indeed impressive) @Joe Conner Which LO or OOo version are you using? -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3399230.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 10/6/2011 2:41 AM, Steve Edmonds wrote: On 06/10/11 17:53, JOE CONNER wrote: http://nabble.documentfoundation.org/file/n3398776/XNumbers_01.ods XNumbers_01.ods -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3398776.html Sent from the Users mailing list archive at Nabble.com. I get a lot of #NAME? in 3.3.4, should the functions work, macros are enabled. Are all functions, or just some of them, giving the #NAME error? This was originally from an EXCEL file, loaded into OOo 3.3.0 which seems to function correctly. Then it was saved as an ODS. If this is not fully implemented by LO then there has been a regression since the fork from OOo. Joe -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
I have done the same thing with google docs and it did it with no errors. -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397360.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 10/6/2011 12:10 PM, Andreas Säger wrote: Am 06.10.2011 20:15, JOE Conner wrote: Are all functions, or just some of them, giving the #NAME error? This was originally from an EXCEL file, loaded into OOo 3.3.0 which seems to function correctly. Then it was saved as an ODS. If this is not fully implemented by LO then there has been a regression since the fork from OOo. Joe Basic function are not found when they are not stored in a library Standard. The program will not load each and every Basic module in order until it finds a certain cell function. I think I understand. You are essentially saying that OOo has functions in the standard library that LO does not - correct? Joe -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Am 07.10.2011 00:00, JOE Conner wrote: I think I understand. You are essentially saying that OOo has functions in the standard library that LO does not - correct? Joe No, all I say is that the user defined functions are in the wrong library. The code can not be found which raises #NAME errors because of the unknown function names. After moving the module to Standard and reloading the document, all the cells are calculated, albeit very, very slowly because Basic is inappropriate for this type of cell functions. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
That is not a bug. As someone told me long ago: This is a natural consequence of doing calculations with finite precision. (And, in this case, using base 2.) If you try the same in Excel, Gnumeric or any other spreadsheet you will get the same (or similar) errors around 4.1 or 4.0 You can do a workaround but I think there is no solution... -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397167.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Juan Carlos wrote: I have done the same thing with google docs and it did it with no errors. Actually, Google just cheats :) It only allows 10 decimal cases. Since the first error in Excel at 4.0 requires 14 decimal cases to be shown it will never show up in Google Docs. Do this calculation in Google Docs and you can test for yourself that it is using Base 2 calculations as well 4095414.77 - 4095398.34 = Sorry about the bad news... -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397411.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Here is a workaround: Type 5.5 in A1 and then type =ROUND(A1-0.1,1) in A2 (make sure the decimal and separator are the same for you) Then just select A2 and drag it down ;) -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397462.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 10/5/2011 9:59 AM, Pedro wrote: That is not a bug. As someone told me long ago: This is a natural consequence of doing calculations with finite precision. (And, in this case, using base 2.) If you try the same in Excel, Gnumeric or any other spreadsheet you will get the same (or similar) errors around 4.1 or 4.0 You can do a workaround but I think there is no solution... -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397167.html Sent from the Users mailing list archive at Nabble.com. Perhaps the attached XNumbers spreadsheet can help. Joe Conner, Poulsbo, WA USA -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
JOE Conner wrote: Perhaps the attached XNumbers spreadsheet can help. This mailing list doesn't allow attached files. Please upload it somewhere or explain what you did ;) -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397636.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Hi :) @ Pedro, very nice work-around and good explanation. Thanks for that and thanks for clarifying that we are not alone there :) @ Joe, Nabble allows uploads for free and helps keep the upload with the thread for people reading this in the future. This thread http://nabble.documentfoundation.org/template/NamlServlet.jtp?macro=replynode=3397636 In Nabble click on Reply and then on More Options. then you get a fairly familiar upload thing very much the same as uploading an attachment to an email. Nabble then uploads and pastes a link to the file straight into the message box. It's really nice to see Nabble working again after the problems over the last week or so. Regards from Tom :) -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397671.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 10/5/2011 12:25 PM, Pedro wrote: JOE Conner wrote: Perhaps the attached XNumbers spreadsheet can help. This mailing list doesn't allow attached files. Please upload it somewhere or explain what you did ;) -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397636.html Sent from the Users mailing list archive at Nabble.com. I included the OP as an addressee. He who had the problem also got this possible solution. Joe -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 10/5/2011 12:37 PM, Tom wrote: Hi :) @ Pedro, very nice work-around and good explanation. Thanks for that and thanks for clarifying that we are not alone there :) @ Joe, Nabble allows uploads for free and helps keep the upload with the thread for people reading this in the future. This thread http://nabble.documentfoundation.org/template/NamlServlet.jtp?macro=replynode=3397636 In Nabble click on Reply and then on More Options. then you get a fairly familiar upload thing very much the same as uploading an attachment to an email. Nabble then uploads and pastes a link to the file straight into the message box. It's really nice to see Nabble working again after the problems over the last week or so. Regards from Tom :) -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397671.html Sent from the Users mailing list archive at Nabble.com. I went to your link, I see the message stream, I do not see a reply button. A total waste of time. Joe -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Hi On Wed, 2011-10-05 at 11:15 -0700, Pedro wrote: Juan Carlos wrote: I have done the same thing with google docs and it did it with no errors. Actually, Google just cheats :) It only allows 10 decimal cases. Since the first error in Excel at 4.0 requires 14 decimal cases to be shown it will never show up in Google Docs. Do this calculation in Google Docs and you can test for yourself that it is using Base 2 calculations as well 4095414.77 - 4095398.34 = Sorry about the bad news... -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397411.html Sent from the Users mailing list archive at Nabble.com. This is not just a spreadsheet issue but related to the precision of the floating point numbers with computers. Basically the more bytes used for the number the better the precision. A general rule it is easier to get more precision with 64 bit computers than 32 bit computers, however software may limit the precision for compatibility reasons to the equivalent of 32 bit. A related issue, not a computer only problem, occurs when you subtract two numbers the precision of the answer drops drastically. In the above example the answer is 16.43. If the two numbers were measurements, the significant figures state that each number is only accurate to about +/- 0.02 and when subtracted the error is basically additive. Division is another area were you can get some wacky precision effects especially when dividing a small number with a large number. This is called propagation of error. The calculations that are done on real data the worse the error is in the final answer, this is true if you did the calculations with a pen and paper, calculator, or computer. Calculating the propagation can be very tedious. Often the problem of the underlying precision of the data is more significant than the computer's precision, but it is not always true. To fully understand the effects, one should do some propagation of error based on the data and on the computer's precision. If you saw some number in a spreadsheet for the above that was slightly different than 16.43 it is fundamentally due to the precision of all floating arithmetic on computers. The spreadsheet may make worse by rounding the floating precision to a lower one than the computer can handle. When I did the calculation on my cell phone I got 16.43168 and using Calc 16.430002. If you want a more detailed discussion on precision problems, both from the data and from the computer, consult a numerical methods text. -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 2011-10-06 10:52, planas wrote: Hi On Wed, 2011-10-05 at 11:15 -0700, Pedro wrote: Juan Carlos wrote: I have done the same thing with google docs and it did it with no errors. Actually, Google just cheats :) It only allows 10 decimal cases. Since the first error in Excel at 4.0 requires 14 decimal cases to be shown it will never show up in Google Docs. Do this calculation in Google Docs and you can test for yourself that it is using Base 2 calculations as well 4095414.77 - 4095398.34 = Sorry about the bad news... -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397411.html Sent from the Users mailing list archive at Nabble.com. This is not just a spreadsheet issue but related to the precision of the floating point numbers with computers. Basically the more bytes used for the number the better the precision. A general rule it is easier to get more precision with 64 bit computers than 32 bit computers, however software may limit the precision for compatibility reasons to the equivalent of 32 bit. A related issue, not a computer only problem, occurs when you subtract two numbers the precision of the answer drops drastically. In the above example the answer is 16.43. If the two numbers were measurements, the significant figures state that each number is only accurate to about +/- 0.02 and when subtracted the error is basically additive. Division is another area were you can get some wacky precision effects especially when dividing a small number with a large number. This is called propagation of error. The calculations that are done on real data the worse the error is in the final answer, this is true if you did the calculations with a pen and paper, calculator, or computer. Calculating the propagation can be very tedious. Often the problem of the underlying precision of the data is more significant than the computer's precision, but it is not always true. To fully understand the effects, one should do some propagation of error based on the data and on the computer's precision. If you saw some number in a spreadsheet for the above that was slightly different than 16.43 it is fundamentally due to the precision of all floating arithmetic on computers. The spreadsheet may make worse by rounding the floating precision to a lower one than the computer can handle. When I did the calculation on my cell phone I got 16.43168 and using Calc 16.430002. If you want a more detailed discussion on precision problems, both from the data and from the computer, consult a numerical methods text. Thats interesting. In Calc I get 16.430001676 and in Kcalc I get 16.43 (to 20 decimal places) -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Steve Edmonds wrote: Thats interesting. In Calc I get 16.430001676 and in Kcalc I get 16.43 (to 20 decimal places) That is expected. Because calculators have a limited number of functions they use finite precision (instead of the base 2 calculations) This means that for this kind of calculations you are better off using a cheap calculator than your powerful PC with a spreadsheet :) -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3398248.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On Thu, 2011-10-06 at 11:30 +1300, Steve Edmonds wrote: On 2011-10-06 10:52, planas wrote: Hi On Wed, 2011-10-05 at 11:15 -0700, Pedro wrote: Juan Carlos wrote: I have done the same thing with google docs and it did it with no errors. Actually, Google just cheats :) It only allows 10 decimal cases. Since the first error in Excel at 4.0 requires 14 decimal cases to be shown it will never show up in Google Docs. Do this calculation in Google Docs and you can test for yourself that it is using Base 2 calculations as well 4095414.77 - 4095398.34 = Sorry about the bad news... -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397411.html Sent from the Users mailing list archive at Nabble.com. This is not just a spreadsheet issue but related to the precision of the floating point numbers with computers. Basically the more bytes used for the number the better the precision. A general rule it is easier to get more precision with 64 bit computers than 32 bit computers, however software may limit the precision for compatibility reasons to the equivalent of 32 bit. A related issue, not a computer only problem, occurs when you subtract two numbers the precision of the answer drops drastically. In the above example the answer is 16.43. If the two numbers were measurements, the significant figures state that each number is only accurate to about +/- 0.02 and when subtracted the error is basically additive. Division is another area were you can get some wacky precision effects especially when dividing a small number with a large number. This is called propagation of error. The calculations that are done on real data the worse the error is in the final answer, this is true if you did the calculations with a pen and paper, calculator, or computer. Calculating the propagation can be very tedious. Often the problem of the underlying precision of the data is more significant than the computer's precision, but it is not always true. To fully understand the effects, one should do some propagation of error based on the data and on the computer's precision. If you saw some number in a spreadsheet for the above that was slightly different than 16.43 it is fundamentally due to the precision of all floating arithmetic on computers. The spreadsheet may make worse by rounding the floating precision to a lower one than the computer can handle. When I did the calculation on my cell phone I got 16.43168 and using Calc 16.430002. If you want a more detailed discussion on precision problems, both from the data and from the computer, consult a numerical methods text. Thats interesting. In Calc I get 16.430001676 and in Kcalc I get 16.43 (to 20 decimal places) That is not surprising, the exact rounding errors will vary in Kspread I get 16.430002. What is interesting is I have only seen positive errors reported (actual true), with enough random data it should be 50/50 positive to negative. -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On Wed, 2011-10-05 at 15:54 -0700, Pedro wrote: Steve Edmonds wrote: Thats interesting. In Calc I get 16.430001676 and in Kcalc I get 16.43 (to 20 decimal places) That is expected. Because calculators have a limited number of functions they use finite precision (instead of the base 2 calculations) This means that for this kind of calculations you are better off using a cheap calculator than your powerful PC with a spreadsheet :) The issue is the internal representation of floating point numbers. But often the machine errors are much less than the noise in the data. In terms of significant figures, the correct answer is 16.43 not 16.43000-00?. The extra digits beyond the 3 are dropped for significant figures. One should actually display only the correct significant figures in the final answer. -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3398248.html Sent from the Users mailing list archive at Nabble.com. -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
On 2011-10-06 12:21, planas wrote: On Thu, 2011-10-06 at 11:30 +1300, Steve Edmonds wrote: On 2011-10-06 10:52, planas wrote: Hi On Wed, 2011-10-05 at 11:15 -0700, Pedro wrote: Juan Carlos wrote: I have done the same thing with google docs and it did it with no errors. Actually, Google just cheats :) It only allows 10 decimal cases. Since the first error in Excel at 4.0 requires 14 decimal cases to be shown it will never show up in Google Docs. Do this calculation in Google Docs and you can test for yourself that it is using Base 2 calculations as well 4095414.77 - 4095398.34 = Sorry about the bad news... -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3397411.html Sent from the Users mailing list archive at Nabble.com. This is not just a spreadsheet issue but related to the precision of the floating point numbers with computers. Basically the more bytes used for the number the better the precision. A general rule it is easier to get more precision with 64 bit computers than 32 bit computers, however software may limit the precision for compatibility reasons to the equivalent of 32 bit. A related issue, not a computer only problem, occurs when you subtract two numbers the precision of the answer drops drastically. In the above example the answer is 16.43. If the two numbers were measurements, the significant figures state that each number is only accurate to about +/- 0.02 and when subtracted the error is basically additive. Division is another area were you can get some wacky precision effects especially when dividing a small number with a large number. This is called propagation of error. The calculations that are done on real data the worse the error is in the final answer, this is true if you did the calculations with a pen and paper, calculator, or computer. Calculating the propagation can be very tedious. Often the problem of the underlying precision of the data is more significant than the computer's precision, but it is not always true. To fully understand the effects, one should do some propagation of error based on the data and on the computer's precision. If you saw some number in a spreadsheet for the above that was slightly different than 16.43 it is fundamentally due to the precision of all floating arithmetic on computers. The spreadsheet may make worse by rounding the floating precision to a lower one than the computer can handle. When I did the calculation on my cell phone I got 16.43168 and using Calc 16.430002. If you want a more detailed discussion on precision problems, both from the data and from the computer, consult a numerical methods text. Thats interesting. In Calc I get 16.430001676 and in Kcalc I get 16.43 (to 20 decimal places) That is not surprising, the exact rounding errors will vary in Kspread I get 16.430002. What is interesting is I have only seen positive errors reported (actual true), with enough random data it should be 50/50 positive to negative. If you do it with the decimal part the other way around it is under. 4095414.34 - 4095398.77 -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
Hi :) Sorry Joe for wasting so much of your time. The correct link was http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-td3397081.html#a3397671 The link i gave led you to the screen you get after pressing the Reply button so the More Options was already visible. Apols and regards from Tom :) -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3398431.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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] Re: LOcalc shows an inaccurate output when use cells autofill function
http://nabble.documentfoundation.org/file/n3398776/XNumbers_01.ods XNumbers_01.ods -- View this message in context: http://nabble.documentfoundation.org/LOcalc-shows-an-inaccurate-output-when-use-cells-autofill-function-tp3397081p3398776.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
