It appears that at least three major spreadsheets, Excel, Apache OpenOffice Cal and gnumeric have a problem with the correct order of operations when dealing with exponents. The gnumeric result is very strange. This problem has probably been reported before but just in case it has not, it would appear to be one more serious problem with spreadsheets. It might be useful in warning people away from using a spreadsheet for serious analysis. Excel -2^2 = 4 2^2^3 = 64 Apache OpenOffice -2^2 = 4 2^2^3 = 64 gnumeric # note one correct, one error! -2^2 = 4 2^2^3 = 256 John Kane Kingston ON Canada ____________________________________________________________ FREE 3D EARTH SCREENSAVER - Watch the Earth right on your desktop!
On Fri, Sep 18, 2015 at 8:39 AM, John Kane <jrkrideau at inbox.com> wrote:> It appears that at least three major spreadsheets, Excel, Apache > OpenOffice Cal and gnumeric have a problem with the correct order of > operations when dealing with exponents. The gnumeric result is very strange. > > This problem has probably been reported before but just in case it has > not, it would appear to be one more serious problem with spreadsheets. It > might be useful in warning people away from using a spreadsheet for serious > analysis. > > Excel > > -2^2 = 4 > > 2^2^3 = 64 > > Apache OpenOffice > > -2^2 = 4 > > 2^2^3 = 64 >My opinion: One correct, one error!? R agrees with me on this:> 2^2[1] 4> 2^2^3[1] 256> 2^(2^3)[1] 256> -2^2[1] -4> (-2)^2[1] 4>> > gnumeric # note one correct, one error! >?My opinion: two correct!?> > -2^2 = 4 > > 2^2^3 = 256 > > John Kane > Kingston ON Canada > >?Seems to be a bit off-topic. Unless your point to is to use R for important work instead of some spreadsheet. A point with which I completely agree!? ?MS-Excel, and Apache OpenOffice, appear to implement the above as (2^2)^3==64. ?Whereas gnumeric implements appears to implement this as: 2^(2^3)==256. Which is "correct"? Depends on whom you ask. ref: https://en.wikipedia.org/wiki/Order_of_operations <quote> If exponentiation is indicated by stacked symbols, the usual rule is to work from the top down, thus: [image: a^{b^c} = a^{(b^c)}], which typically is not equal to [image: (a^b)^c]. However, some computer systems may resolve the ambiguous expression differently. For example, Microsoft Office Excel <https://en.wikipedia.org/wiki/Microsoft_Office_Excel> evaluates *a*^*b*^*c* as (*a*^*b*)^*c*, which is opposite of normally accepted convention of top-down order of execution for exponentiation. If a=4, p=3, and q=2, [image: a^{p^q}] is evaluated to 4096 in Microsoft Excel 2013, the same as [image: (a^p)^q]. The expression [image: a^{(p^q)}], on the other hand, results in 262144 using the same program. </quote> ?Gnumeric abides by the above definition. FWIW. BTW - MS-Excel also has 1900 as a friggin' leap year (due to Lotus 1-2-3 apparently), so I don't consider MS-Excel (or anything else from MS for that matter) to be a definitive source of correctness.? Personal opinion. FSF associate member. Penguinista. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown [[alternative HTML version deleted]]
Unfortunately the order of operations is not universal in computing. The real question is whether a program performs the way it is documented. Excel documents that unary operations take precedence over exponentiation and that within groups, the order is left to right. LibreOffice Calc behaves as Excel, but does not document the order of operations except to say */ before +-, left to right. I couldn't find any statement about the order of operations in the documentation for Gnumeric. R documents that unary operations come after exponentiation and, within exponentiation, the order is right to left. Fortran puts unary operations with addition and subtraction after exponentiation with exponentiation right to left. C does not have an exponentiation operator, but unary operations come before multiplication and division. When in doubt, use parentheses to make sure you get what you want. ------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77840-4352 -----Original Message----- From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of John McKown Sent: Friday, September 18, 2015 9:31 AM To: John Kane Cc: r-help Subject: Re: [R] Spreadsheet math problem (exponentiation) On Fri, Sep 18, 2015 at 8:39 AM, John Kane <jrkrideau at inbox.com> wrote:> It appears that at least three major spreadsheets, Excel, Apache > OpenOffice Cal and gnumeric have a problem with the correct order of > operations when dealing with exponents. The gnumeric result is very strange. > > This problem has probably been reported before but just in case it has > not, it would appear to be one more serious problem with spreadsheets. It > might be useful in warning people away from using a spreadsheet for serious > analysis. > > Excel > > -2^2 = 4 > > 2^2^3 = 64 > > Apache OpenOffice > > -2^2 = 4 > > 2^2^3 = 64 >My opinion: One correct, one error!? R agrees with me on this:> 2^2[1] 4> 2^2^3[1] 256> 2^(2^3)[1] 256> -2^2[1] -4> (-2)^2[1] 4>> > gnumeric # note one correct, one error! >?My opinion: two correct!?> > -2^2 = 4 > > 2^2^3 = 256 > > John Kane > Kingston ON Canada > >?Seems to be a bit off-topic. Unless your point to is to use R for important work instead of some spreadsheet. A point with which I completely agree!? ?MS-Excel, and Apache OpenOffice, appear to implement the above as (2^2)^3==64. ?Whereas gnumeric implements appears to implement this as: 2^(2^3)==256. Which is "correct"? Depends on whom you ask. ref: https://en.wikipedia.org/wiki/Order_of_operations <quote> If exponentiation is indicated by stacked symbols, the usual rule is to work from the top down, thus: [image: a^{b^c} = a^{(b^c)}], which typically is not equal to [image: (a^b)^c]. However, some computer systems may resolve the ambiguous expression differently. For example, Microsoft Office Excel <https://en.wikipedia.org/wiki/Microsoft_Office_Excel> evaluates *a*^*b*^*c* as (*a*^*b*)^*c*, which is opposite of normally accepted convention of top-down order of execution for exponentiation. If a=4, p=3, and q=2, [image: a^{p^q}] is evaluated to 4096 in Microsoft Excel 2013, the same as [image: (a^p)^q]. The expression [image: a^{(p^q)}], on the other hand, results in 262144 using the same program. </quote> ?Gnumeric abides by the above definition. FWIW. BTW - MS-Excel also has 1900 as a friggin' leap year (due to Lotus 1-2-3 apparently), so I don't consider MS-Excel (or anything else from MS for that matter) to be a definitive source of correctness.? Personal opinion. FSF associate member. Penguinista. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown [[alternative HTML version deleted]] ______________________________________________ R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
> On 18 Sep 2015, at 16:31, John McKown <john.archie.mckown at gmail.com> wrote: > > On Fri, Sep 18, 2015 at 8:39 AM, John Kane <jrkrideau at inbox.com> wrote: > >> It appears that at least three major spreadsheets, Excel, Apache >> OpenOffice Cal and gnumeric have a problem with the correct order of >> operations when dealing with exponents. The gnumeric result is very strange. >> >> This problem has probably been reported before but just in case it has >> not, it would appear to be one more serious problem with spreadsheets. It >> might be useful in warning people away from using a spreadsheet for serious >> analysis. >> >> Excel >> >> -2^2 = 4 >> >> 2^2^3 = 64 >> >> Apache OpenOffice >> >> -2^2 = 4 >> >> 2^2^3 = 64 >> > > My opinion: One correct, one error!? R agrees with me on this: >> 2^2 > [1] 4 >> 2^2^3 > [1] 256 >> 2^(2^3) > [1] 256 >> -2^2 > [1] -4 >> (-2)^2 > [1] 4 >> > > > > >> >> gnumeric # note one correct, one error! >> > > ?My opinion: two correct!? > >I don?t agree. All are wrong according to standard math rules except Gnumeric with the exponentiation. R is correct. See https://en.wikipedia.org/wiki/Order_of_operations Lesson: always use parentheses to make absolutely clear what you mean. Berend> >> -2^2 = 4 >> >> 2^2^3 = 256 >> >> John Kane >> Kingston ON Canada >> >> > ?Seems to be a bit off-topic. Unless your point to is to use R for > important work instead of some spreadsheet. A point with which I completely > agree!? > > > ?MS-Excel, and Apache OpenOffice, appear to implement the above as > (2^2)^3==64. ?Whereas gnumeric implements appears to implement this as: > 2^(2^3)==256. Which is "correct"? Depends on whom you ask. > > ref: https://en.wikipedia.org/wiki/Order_of_operations > <quote> > > If exponentiation is indicated by stacked symbols, the usual rule is to > work from the top down, thus: > [image: a^{b^c} = a^{(b^c)}], > > which typically is not equal to [image: (a^b)^c]. However, some computer > systems may resolve the ambiguous expression differently. For example, > Microsoft > Office Excel <https://en.wikipedia.org/wiki/Microsoft_Office_Excel> > evaluates *a*^*b*^*c* as (*a*^*b*)^*c*, which is opposite of normally > accepted convention of top-down order of execution for exponentiation. If > a=4, p=3, and q=2, [image: a^{p^q}] is evaluated to 4096 in Microsoft Excel > 2013, the same as [image: (a^p)^q]. The expression [image: a^{(p^q)}], on > the other hand, results in 262144 using the same program. > </quote> > > ?Gnumeric abides by the above definition. FWIW. BTW - MS-Excel also has > 1900 as a friggin' leap year (due to Lotus 1-2-3 apparently), so I don't > consider MS-Excel (or anything else from MS for that matter) to be a > definitive source of correctness.? Personal opinion. FSF associate member. > Penguinista. > > -- > > Schrodinger's backup: The condition of any backup is unknown until a > restore is attempted. > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
Let me add a little bit here: When using math formulas, one should know about the parsing rules form complex expression which do not have all the necessary parenthesis. Different systems do have different parings rules. In the case of a^b^c, the expression is ambiguus because (as mentioned in a previous mail) in general (a^b)^c != a^(b^c) To avoid unintended consequences, just us parentheses and you will get the right result. in the case of -a^b The question is the order of precedence of unary - and binary ^. In Excel, -2^2=4, but 0-2^2=-4 Reason: For Excel, unary - is stronger than the power operator, but binary minus is weaker. My feeling is that too many people are bashing spreadsheets for the wrong reason. Spreadsheets ca do things R cannot do: Automatic recalculation when input changes, and visual point and click modelling of dependencies. The calculation engine of Excel admittedly has some weak points. That is the reason why I wrote RExcel which gives you all the advantages of the spreadsheet interface and allows you to use the R calculation within this interface whenever needed. -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 670 bytes Desc: Message signed with OpenPGP using GPGMail URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20150918/dbe37d89/attachment.bin>
> On 18 Sep 2015, at 16:31 , John McKown <john.archie.mckown at gmail.com> wrote: > > ref: https://en.wikipedia.org/wiki/Order_of_operations > <quote> > > If exponentiation is indicated by stacked symbols, the usual rule is to > work from the top down, thus: > [image: a^{b^c} = a^{(b^c)}],[snip] ...and it might be added that this is (in "paper math") because (a^b)^c==a^{bc} and there would be no point in writing a^b^c if you might as well have written a^{bc}. (The curly braces only being there to indicate that the exponent is bc.) Of course this sort of consideration hasn't always had effect on programmers, so you do need to check. -- Peter Dalgaard, Professor, Center for Statistics, Copenhagen Business School Solbjerg Plads 3, 2000 Frederiksberg, Denmark Phone: (+45)38153501 Email: pd.mes at cbs.dk Priv: PDalgd at gmail.com