BIMDAS - Order of Calculation
in [General]
|
From: Atreides on 21 Feb 2005 22:47 I've recently noticed that Excel flies in the face of standard scientific, mathematical and engineering convention in the calculation of powers for numbers that are then multiplied by a negative. The convention of mathematics, "BIMDAS" (or similar acronyms), states that _I_ndices (or powers, or exponents), should be calculated before _M_ultiplication. Because of this, the following is accepted as correct: -3^2 = -9. This is because it is the equivalent of (-1) * 3^2 = -1 * 9 = -9 However, Excel chooses to recognise this as (-3)^2 = 9. This error is particularly problematic when doing algebraic computations in such a tool as Mathematica and then copying the result into Excel in input form. That is, -x^2 -> -A1^2 -> -A1^2 (Mathematica) (Mathematica with reference substituted) (Excel) To correct the error, one must manually change it to: -(A1^2) QUESTIONS 1: Why does Excel have this convention! 2: Is there any way to change it/make is more convenient? I've only recently noticed this (which is quite scary to think how many errors I may have made in the past!) Thanks for your time, Cheers, Peter
From: Myrna Larson on 21 Feb 2005 22:49 Why? Because that's the way they wrote it. And no, there's no way to change this. You must write your Excel formulas to a suit the way Excel does the calculations. On Mon, 21 Feb 2005 19:47:02 -0800, "Atreides" <atreides1AThotmailD0Tcom> wrote: >I've recently noticed that Excel flies in the face of standard scientific, >mathematical and engineering convention in the calculation of powers for >numbers that are then multiplied by a negative. > >The convention of mathematics, "BIMDAS" (or similar acronyms), states that >_I_ndices (or powers, or exponents), should be calculated before >_M_ultiplication. Because of this, the following is accepted as correct: > >-3^2 = -9. > >This is because it is the equivalent of (-1) * 3^2 = -1 * 9 = -9 > >However, Excel chooses to recognise this as (-3)^2 = 9. > >This error is particularly problematic when doing algebraic computations in >such a tool as Mathematica and then copying the result into Excel in input >form. That is, > > -x^2 -> -A1^2 >-> -A1^2 >(Mathematica) (Mathematica with reference substituted) (Excel) > >To correct the error, one must manually change it to: -(A1^2) > >QUESTIONS >1: Why does Excel have this convention! >2: Is there any way to change it/make is more convenient? > >I've only recently noticed this (which is quite scary to think how many >errors I may have made in the past!) > >Thanks for your time, > >Cheers, >Peter
From: Atreides on 21 Feb 2005 23:15 > Why? Because that's the way they wrote it. Why is the sky blue? Because. ;) I was hoping for something more informative than this. e.g. 1. This convention was considered more intuitive to the majority of expected users. 2. Computer programmers live in their own world and have their own conventions. 3. Other... > And no, there's no way to change this. Perhaps this should be included in the next version of Excel. Some other options can be changed with regards to the calculations (by going to "Tools", "Options", "Calculation"). This would be quite a useful feature. Thanks Peter
From: Royman101 on 21 Feb 2005 23:59 Maybe is skewed. I was taught that -3^2=9, and not -9. The integer used is -3, not -1*3. You may think I was taught wrong. I submit to you I was not taught in this country, rather, I grew up in Europe. Could it be that Excel had some European programmers.... Roy "Atreides" <atreides1AThotmailD0Tcom> wrote in message news:3D9F798F-E87A-4EE9-965A-6643085AE636(a)microsoft.com... > > Why? Because that's the way they wrote it. > > Why is the sky blue? Because. ;) > > I was hoping for something more informative than this. e.g. > 1. This convention was considered more intuitive to the majority of expected > users. > 2. Computer programmers live in their own world and have their own > conventions. > 3. Other... > > > And no, there's no way to change this. > > Perhaps this should be included in the next version of Excel. Some other > options can be changed with regards to the calculations (by going to "Tools", > "Options", "Calculation"). This would be quite a useful feature. > > Thanks > Peter
From: Harlan Grove on 22 Feb 2005 00:14
"Atreides" <atreides1AThotmailD0Tcom> wrote... .... >I was hoping for something more informative than this. e.g. >1. This convention was considered more intuitive to the majority of >expected users. .... Perhaps now, but not necessarily back when spreadsheets made their big debut in the mid 1980s. FWLIW, this is COBOL's sign convention, and maybe it wasn't unreasonable for Microsoft's original Excel programmers to decide that it'd be a good idea to follow COBOL operator precedence. Then again, Lotus 123 follows standard mathematical conventions and gives exponentiation higher precedence than unary minus (sign change), so Microsoft's original programmers gave Excel a different operator precedence convention than the leading spreadsheet on the market back when they were developing the original version of Excel. That alone makes it VERY LIKELY this was a design screw-up, but once made it can't be unmade because it'd break existing formulas relying on current operator precedence. If you really believe you want to read about this, follow these archived threads. http://groups-beta.google.com/group/sci.math/browse_frm/thread/81214f129ec55664 http://groups-beta.google.com/group/microsoft.public.excel.programming/browse_frm/thread/74fc5978cbbd95a8 >>And no, there's no way to change this. > >Perhaps this should be included in the next version of Excel. Some other >options can be changed with regards to the calculations (by going to >"Tools", "Options", "Calculation"). This would be quite a useful feature. Don't count on this happening. Excel's formula parser, in which its operator precedence is implemented, seems to be one of the oldest, most myopically designed bits of code in all of Excel. If Microsoft hasn't made any fundamental changes since Excel 4 (3D referencing within .XLW workbooks), over a decade ago, why would you believe they have any inclination to fix this any time soon? Also, flipping operator precedence on the fly would require a different formula parser of every possible operator precedence combination. That'd add considerable bulk to the Excel .EXE - not a good thing. |