Home |
Search |
Today's Posts |
#1
|
|||
|
|||
BIMDAS - Order of Calculation
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"Atreides" <atreides1AThotmailD0Tcom wrote in message
... 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 Hi Peter, See this thread for a *very* comprehensive discussion on the topic: http://groups.google.co.nz/groups?hl...umbus. rr.com Or this (same thing but shorter URL): http://tinyurl.com/5t69s Enjoy! Alan. |
#5
|
|||
|
|||
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 ... 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 |
#6
|
|||
|
|||
"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/...214f129ec55664 http://groups-beta.google.com/group/...fc5978cbbd95a8 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. |
#7
|
|||
|
|||
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.... This could have something to do with it. However, the convention -x^2 == -(x^2) is not negotiable when you are working with algebra. If you are dealing with real numbers, then writing -x^2 would be pointless if -x^2 really means (-x)^2 == ( -1)^2 * (x)^2 = x^2 Therefore, if the "European convention" you mention holds, then -x^2 == x^2. (!) So then, -x^2 should be taken as -(x^2), to mean the opposite of the square of x, and to differentiate it from simplifying to x^2. Cheers, Peter **PS** The post that Alan mentioned (http://tinyurl.com/5t69s) is good reading on this topic. Apparently the issue cost someone $2000 in time and effort to resolve! The convention as I've learnt it is that the power operator is only applied to the argument immediately under the indice. So for instance: 2 * 3^2 = 2 * 9 = 18 AND (2 * 3)^2 = 6^2 = 36 (2 * 3)^2 = 2^2 * 3 ^ 2 = 4 * 9 = 36 Therefore, if the negative is meant to be included in the power, it should be included in brackets. So then: -3^2 = -1 * 3^2 AND (-3)^2 = 9 (-3)^2 = (-1 * 3)^2 = (-1)^2 * (3)^2 = 1 * 9 = 9 |
#8
|
|||
|
|||
|
#10
|
|||
|
|||
Doesn't -(x^2) satisfy -9?
-- Ken Russell Remove yourhat to reply by e-mail .. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... But then Ken, what reason would one have for writing: -x^2 if it really simplifies down to -x * -x = x^2? How would one write the opposite of the square of x? Cheers, Peter "Ken Russell" wrote: -3^2 is the same as -3*-3 which equals 9 not -9 At least that's what I was taught over 60 years ago. -- Ken Russell Remove yourhat to reply by e-mail .. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... 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 |
#11
|
|||
|
|||
Doesn't -(x^2) satisfy -9?
Yes, according to your convention, you would have to write -(x^2). So consider the two systems we could have: 1. x^2 the opposite of which is -x^2 2. x^2 the opposite of which is -(x^2) System number one (which, in my experience, is standard in maths, science and engineering textbooks) makes more sense to me. Indices or powers have precedence over multiplication. Consider graphs of parabolas. According to your definition, the graphs: y = x^2 AND y = -x^2 would be the same graph. However, in my experience, eveyone knows that y = x^2 is a "U" shape while y = -x^2 is an "upsidedown-U" shape. Would you agree? Cheers, Peter "Ken Russell" wrote: -- Ken Russell Remove yourhat to reply by e-mail .. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... But then Ken, what reason would one have for writing: -x^2 if it really simplifies down to -x * -x = x^2? How would one write the opposite of the square of x? Cheers, Peter "Ken Russell" wrote: -3^2 is the same as -3*-3 which equals 9 not -9 At least that's what I was taught over 60 years ago. -- Ken Russell Remove yourhat to reply by e-mail .. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... 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 |
#12
|
|||
|
|||
I am a simple man who bows to your superior knowledge. At least all my
maths teachers are dead now :-) Cheers, Ken Russell Remove yourhat to reply by e-mail .. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... Doesn't -(x^2) satisfy -9? Yes, according to your convention, you would have to write -(x^2). So consider the two systems we could have: 1. x^2 the opposite of which is -x^2 2. x^2 the opposite of which is -(x^2) System number one (which, in my experience, is standard in maths, science and engineering textbooks) makes more sense to me. Indices or powers have precedence over multiplication. Consider graphs of parabolas. According to your definition, the graphs: y = x^2 AND y = -x^2 would be the same graph. However, in my experience, eveyone knows that y = x^2 is a "U" shape while y = -x^2 is an "upsidedown-U" shape. Would you agree? Cheers, Peter "Ken Russell" wrote: -- Ken Russell Remove yourhat to reply by e-mail .. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... But then Ken, what reason would one have for writing: -x^2 if it really simplifies down to -x * -x = x^2? How would one write the opposite of the square of x? Cheers, Peter "Ken Russell" wrote: -3^2 is the same as -3*-3 which equals 9 not -9 At least that's what I was taught over 60 years ago. -- Ken Russell Remove yourhat to reply by e-mail .. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... 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 |
#13
|
|||
|
|||
Ken, I am in immense respect of you! I've just read the entire topic on this
bug on the google newsgroup... no-one really listening, just arguing, ego's flying etc. Your humilty and gentleness is very pleasantly refreshing! Cheers to you! :) |
#14
|
|||
|
|||
I think the consensus is that Excel is wrong in this calculation, and most
likely will never be fixed. I think the problem is that Excel can not look ahead in its interpretation of the equation to see that ^ would come first, and then take the negative of this number. I agree with Harlan as it appears Excel can only read Left to Right, and that's it. A program like Mathematica can read the whole expression correctly. For a small demo, Excel does the following left to right only... =4^3^2 4096 But Mathematica will do this correctly as 4^(3^2) 4^3^2 262144 As you can see, Excel just can't look ahead to do it properly. I've never liked the help file explanation on Operator precedence. "Negation (as in -1)" Negation I think usually means True / False. I think we have to guess that what they mean is that it will flip the sign bit of the number if this is what's seen first (and disregard anything later as in ^). My thoughts are this is not a very good explanation. http://mathworld.wolfram.com/Negation.html Just some other thoughts. A nice feature of Excel though is its ability to interpret text as numbers where appropriate. This is a "nice" feature for Excel, but not for a math program. For example, if A1 had the text '5 You could use = - - A1 to get the number 5. Same with =A1+0 Of course, in Mathematica you could not add the text "5" and zero to get 5. And the - - A1 is the PreDecrement operator, so this would not make sense in Mma. If A1 held the number 5, in Excel, you could have a formula like: =A1--------2 7 This would not make sense in Mathematica. You may want to look at the "InputForm" of Mma equation and use the same "Power" function with Excel. Using Excel's Power function is a good way to make it clear what you are doing. I have experimented with putting a "Hold" around the equation (via "HoldForm") and work with the Power pattern, but never had much luck with this approach myself. Once you put a Release on the hold, the equation will simplify again. I do have a custom //Vba function that transforms the output into the format for Excel's vba, but it doesn't cover everything. For a simple demo. If you were not sure, and want to enter =4^3^2 in Excel, you may want to take a look at how this is done, and use the power function in Excel. FullForm[HoldForm[4^3^2]] Power[4,Power[3,2] I would take the hint and do it like this in Excel =POWER(4,POWER(3,2)) And for those interested ... FullForm[HoldForm[-5^2]] Times[-1, Power[5, 2]] -- Dana DeLouis Win XP & Office 2003 "Atreides" <atreides1AThotmailD0Tcom wrote in message ... Ken, I am in immense respect of you! I've just read the entire topic on this bug on the google newsgroup... no-one really listening, just arguing, ego's flying etc. Your humilty and gentleness is very pleasantly refreshing! Cheers to you! :) |
#15
|
|||
|
|||
Royman101 wrote...
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.... I have a few German language math texts that show equations that clearly show they follow exponentiation before negation operator precedence. Perhaps it may be unreasonable to extrapolate from that that Germany follows the same operator precedence convention as the US, but I'd be willing to bet that was so. |
#16
|
|||
|
|||
Dana DeLouis wrote...
I think the consensus is that Excel is wrong in this calculation, and most likely will never be fixed. I think the problem is that Excel can not look ahead in its interpretation of the equation to see that ^ would come first, and then take the negative of this number. I agree with Harlan as it appears Excel can only read Left to Right, and that's it. A program like Mathematica can read the whole expression correctly. For a small demo, Excel does the following left to right only... =4^3^2 4096 But Mathematica will do this correctly as 4^(3^2) 4^3^2 262144 As you can see, Excel just can't look ahead to do it properly. You're misunderstanding what I wrote *AND* attributing to me things I didn't state. VisiCalc uses simple L-to-R evaluation: 2+3*4 returns 20. 123 uses an operator precedence hierarchy: 2+3*4 returns 14. It gives ^ higher precedence than unary -: -3^2 returns -9. Excel uses an operator precedence hierarchy: 2+3*4 returns 14. However, it gives ^ *lower* precedence than unary -: -3^2 returns 9. With regard to 4^3^2, it has nothing to do with operator *precedence* - it's operator *associativity*. FWIW, most programming languages (other than VB[?] and oddballs like APL and its descendants) provide R-to-L associativity for exponentiation and L-to-R associativity for +-*/ (+ and * are associative for integers, rational, algebraic, real and complex numbers, but not always so for binary floating point 'numbers'). Precedence determines evaluation order of *DIFFERENT* operators. Associativity determines evaluation order of the *SAME* operator applied multiple times in sequence. I've never liked the help file explanation on Operator precedence. "Negation (as in -1)" Negation I think usually means True / False. I think we have to guess that what they mean is that it will flip the sign bit of the number if this is what's seen first (and disregard anything later as in ^). My thoughts are this is not a very good explanation. .... Yeah, yeah. Agreed, but in colloquial discussions it's too much of a PITA to say additive inverse. Sign change may be an alternative. Of course, in Mathematica you could not add the text "5" and zero to get 5. And the - - A1 is the PreDecrement operator, so this would not make sense in Mma. .... Nor in C or all the other languages it's spawned. So use -(-A1). And for those interested ... FullForm[HoldForm[-5^2]] Times[-1, Power[5, 2]] And there are some like me who consider this to be a really stupid approach due to its circular nature. Sign and exponentiation together only make sense in rings, rings are necessarily additive groups, additive groups have well-defined additive inverses, and AdditiveInverse(MultiplicativeIdentity) * x = AdditiveInverse(x) is a derived truth that necessarily relies upon additive inverse to provide -1. So why not express -5^2 as -(5^2) or perhaps ChangeSign[Power[5, 2]] ? This entire problem is due to the laziness of mathematicians in previous centuries who used the same character/token/sign to express numeric sign, sign change and subtraction. An alternative convention might have been to interpret a dash *not* immediately after a complete expression but immediately before a literal number [e.g., x*-3^2 == x * ((-3) ^ 2)] as part of the number (so, technically, not subject to operator precedence because it wouldn't be an operator), but between incomplete expressions with the expression to the right *not* a literal number treat it as a sign change operator with standard precedence [e.g., x*-y^2 == x * (-(y^2))]. One argument against this convention is that you'd need to remember that literal numbers and variables would be treated differently, e.g., y = 3, -3^2 wouldn't equal -y^2. Computers wouldn't have a problem with this now. Lexical analysis precedes syntactic parsing, so just need to include leading - and + as part of literal number tokens when they clearly couldn't be dyadic operators. However that caveat implies the presence of noncapturing assertions in the lexical analyzer, and those weren't part of most regular expression packages until the mid 1980s. |
#17
|
|||
|
|||
Thanks Harlan. I wish there were more of these discussions. I was trying
to come up with another example for the op to show that Excel is unable to do a R-to-L "associative" operation. For the op, I guess the best one can offer is just be aware of Excel's limitations, and try to anticipate the differences between the two programs. (A little off topic I know) For the op, a slightly different point from what was mentioned is that in Mma, the Power function does not have the "Associative property", as it does not have the Flat attribute. Flat corresponds to the mathematical property of associativity. Functions like Times & Plus are "Flat", and associative. Therefore... Power[4, 3, 2] 262144 Attributes[Power] {Listable, NumericFunction, OneIdentity, Protected} Attributes[Times] {Flat, ..., OneIdentity, Orderless, ....} Again, just be aware of the differences... -- Dana DeLouis Win XP & Office 2003 "Harlan Grove" wrote in message oups.com... Dana DeLouis wrote... I think the consensus is that Excel is wrong in this calculation, and most likely will never be fixed. I think the problem is that Excel can not look ahead in its interpretation of the equation to see that ^ would come first, and then take the negative of this number. I agree with Harlan as it appears Excel can only read Left to Right, and that's it. A program like Mathematica can read the whole expression correctly. For a small demo, Excel does the following left to right only... =4^3^2 4096 But Mathematica will do this correctly as 4^(3^2) 4^3^2 262144 As you can see, Excel just can't look ahead to do it properly. You're misunderstanding what I wrote *AND* attributing to me things I didn't state. VisiCalc uses simple L-to-R evaluation: 2+3*4 returns 20. 123 uses an operator precedence hierarchy: 2+3*4 returns 14. It gives ^ higher precedence than unary -: -3^2 returns -9. Excel uses an operator precedence hierarchy: 2+3*4 returns 14. However, it gives ^ *lower* precedence than unary -: -3^2 returns 9. With regard to 4^3^2, it has nothing to do with operator *precedence* - it's operator *associativity*. FWIW, most programming languages (other than VB[?] and oddballs like APL and its descendants) provide R-to-L associativity for exponentiation and L-to-R associativity for +-*/ (+ and * are associative for integers, rational, algebraic, real and complex numbers, but not always so for binary floating point 'numbers'). Precedence determines evaluation order of *DIFFERENT* operators. Associativity determines evaluation order of the *SAME* operator applied multiple times in sequence. I've never liked the help file explanation on Operator precedence. "Negation (as in -1)" Negation I think usually means True / False. I think we have to guess that what they mean is that it will flip the sign bit of the number if this is what's seen first (and disregard anything later as in ^). My thoughts are this is not a very good explanation. ... Yeah, yeah. Agreed, but in colloquial discussions it's too much of a PITA to say additive inverse. Sign change may be an alternative. Of course, in Mathematica you could not add the text "5" and zero to get 5. And the - - A1 is the PreDecrement operator, so this would not make sense in Mma. ... Nor in C or all the other languages it's spawned. So use -(-A1). And for those interested ... FullForm[HoldForm[-5^2]] Times[-1, Power[5, 2]] And there are some like me who consider this to be a really stupid approach due to its circular nature. Sign and exponentiation together only make sense in rings, rings are necessarily additive groups, additive groups have well-defined additive inverses, and AdditiveInverse(MultiplicativeIdentity) * x = AdditiveInverse(x) is a derived truth that necessarily relies upon additive inverse to provide -1. So why not express -5^2 as -(5^2) or perhaps ChangeSign[Power[5, 2]] ? This entire problem is due to the laziness of mathematicians in previous centuries who used the same character/token/sign to express numeric sign, sign change and subtraction. An alternative convention might have been to interpret a dash *not* immediately after a complete expression but immediately before a literal number [e.g., x*-3^2 == x * ((-3) ^ 2)] as part of the number (so, technically, not subject to operator precedence because it wouldn't be an operator), but between incomplete expressions with the expression to the right *not* a literal number treat it as a sign change operator with standard precedence [e.g., x*-y^2 == x * (-(y^2))]. One argument against this convention is that you'd need to remember that literal numbers and variables would be treated differently, e.g., y = 3, -3^2 wouldn't equal -y^2. Computers wouldn't have a problem with this now. Lexical analysis precedes syntactic parsing, so just need to include leading - and + as part of literal number tokens when they clearly couldn't be dyadic operators. However that caveat implies the presence of noncapturing assertions in the lexical analyzer, and those weren't part of most regular expression packages until the mid 1980s. |
#18
|
|||
|
|||
"Atreides" <atreides1AThotmailD0Tcom wrote in message ... 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.... This could have something to do with it. However, the convention -x^2 == -(x^2) is not negotiable when you are working with algebra. If you are dealing with real numbers, then writing -x^2 would be pointless if -x^2 really means (-x)^2 == ( -1)^2 * (x)^2 = x^2 Therefore, if the "European convention" you mention holds, then -x^2 == x^2. (!) I'll add my comment - Yes, this is correct, and this is what I was taught (many moons ago). However, I see you point as well and find myself more agreeable with it than my own 'paradigm', if you will. Thanks! So then, -x^2 should be taken as -(x^2), to mean the opposite of the square of x, and to differentiate it from simplifying to x^2. Cheers, Peter **PS** The post that Alan mentioned (http://tinyurl.com/5t69s) is good reading on this topic. Apparently the issue cost someone $2000 in time and effort to resolve! The convention as I've learnt it is that the power operator is only applied to the argument immediately under the indice. So for instance: 2 * 3^2 = 2 * 9 = 18 AND (2 * 3)^2 = 6^2 = 36 (2 * 3)^2 = 2^2 * 3 ^ 2 = 4 * 9 = 36 Therefore, if the negative is meant to be included in the power, it should be included in brackets. So then: -3^2 = -1 * 3^2 AND (-3)^2 = 9 (-3)^2 = (-1 * 3)^2 = (-1)^2 * (3)^2 = 1 * 9 = 9 |
#19
|
|||
|
|||
Yes, obviously I should have been more careful transferring Mathematica
formuals into Excel. I though that changing the Mma output into "Input Form" would have been safe enough - obviously not. Otherwise one must enter the formulas by hand - not a fun task. I nice trick I've discovered is to make the cell-reference substitutions in Mathematica (e.g. a - G3, b - G4...). This avoids doing it by hand as well. |
#20
|
|||
|
|||
Who would would possibly know why they did it this way? What's the point of
wasting time speculating about WHY? You have a problem to solve. I would think your time would be better spent on that aspect. Fundamentally, you need to add parentheses to an expression like -X^2 so it becomes -(X^2). It is VERY unlikely this will be changed. It could "break" existing spreadsheet formulas that have been written to accommodate Excel's calculation order. "Atreides" <atreides1AThotmailD0Tcom wrote in message ... 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 |
#21
|
|||
|
|||
Who would would possibly know why they did it this way? What's the point of
wasting time speculating about WHY? When confronted with something that is so incredibly against convention (and that I have somehow not noticed for the last 10 or so years), it pulls terribly at my curiosity. Also, to increase my understanding of computers and calculating program. You have a problem to solve. I would think your time would be better spent on that aspect. Fundamentally, you need to add parentheses to an expression like -X^2 so it becomes -(X^2). Yes, this is the obvious fix. However, I have been (and will continue to) copy formulas from Mathematica (in Input Form, which is almost identical to Excel format, except for issues like this). If I could avoid all past and future manual changes to be spreadsheets, this would be incredibly valuable. But alas. Here's to checking assumptions... Cheers, Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining a default worksheet order | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
Data association | Excel Worksheet Functions | |||
time-clock calculation | Excel Worksheet Functions |