Incorrect order of operations in formula
Author 
Message 
Jonathan & Kim Bisho #1 / 9

Incorrect order of operations in formula
I just noticed that Excel does not use correct order of operations with  and ^. For example, the formula =  5 ^ 2 incorrectly displays 25 instead of 25. In other words, x^2 is interpreted as (x)^2, when it should be interpreted as (x^2). This is also a problem with multiplication; 1*5^2 also yields 25. On the other hand, addition is fine; if it wasn't I'd be really worried. So 05^2 yields 25. If anyone can tell me why this makes sense in somebody's world, I'd like to know. Also, if the problem needs to be reported, how do I do it? Thanks, Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


Chip Pearso #2 / 9

Incorrect order of operations in formula
Jonathan, That is indeed the correct order of operation. Negation is the highest (first) priority of standard arithmetic operations. Subtraction, which uses the same symbol as negation, is lower priority than negation or exponentiation, but is a completely different operation. The two formulas =5^2 and =05^2 are NOT the same. In the first, the  is a negation operator. In the second, it is a subtraction operator. This is the way programming languages work. It is not a problem, and does not need to be reported. Cordially, Chip Pearson http://home.gvi.net/~cpearson/excel
Quote: >I just noticed that Excel does not use correct order of operations >with  and ^. For example, the formula =  5 ^ 2 incorrectly displays >25 instead of 25. In other words, x^2 is interpreted as (x)^2, when >it should be interpreted as (x^2). >This is also a problem with multiplication; 1*5^2 also yields 25. >On the other hand, addition is fine; if it wasn't I'd be really worried. >So 05^2 yields 25. >If anyone can tell me why this makes sense in somebody's world, >I'd like to know. >Also, if the problem needs to be reported, how do I do it? >Thanks, >Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


Alan Beba #3 / 9

Incorrect order of operations in formula
This issue was raised in the Excel newsgroups about a year or so ago, and I'm still waiting for someone to cite me their persuasive authority for what they deem to be a "correct" order, or that an expression "should be" interpreted a particular way. Many programmers systematically avoid any problem by routinely using appropriate parentheses to force the intended meaning. My own notion of what's going on is that there is a fairly well accepted notion of a negation operator, , that is different from the subtraction operator, with a different order of precedence in most programming languages, and people try to assert that it should be assigned the same order of precedence as the subtraction operatorbut I've never seen a decent discussion of this. It seemed at the time to be an issue that people got emotional over, but nobody wanted to rationally discuss it. People even expressed the view that it was an arrogant Microsoft innovation to "reverse" the "basic rules". I'm curious why the poster here thinks that there is some natural or unnatural law about the order of precedence in this case. Let's get a good, hot (but civil, of course) thread going.
Quote: > I just noticed that Excel does not use correct order of operations > with  and ^. For example, the formula =  5 ^ 2 incorrectly displays > 25 instead of 25. In other words, x^2 is interpreted as (x)^2, when > it should be interpreted as (x^2). > This is also a problem with multiplication; 1*5^2 also yields 25. > On the other hand, addition is fine; if it wasn't I'd be really worried. > So 05^2 yields 25. > If anyone can tell me why this makes sense in somebody's world, > I'd like to know. > Also, if the problem needs to be reported, how do I do it? > Thanks, > Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


Thomas Ogilv #4 / 9

Incorrect order of operations in formula
Jonathan, You are comparing negation and subtraction as equals, but they are not: You might want to consult the help before reporting this: The order in which Microsoft Excel performs operations in formulas If you combine several operators in a single formula, Microsoft Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence ? for example, if a formula contains both a multiplication and division operator ? Microsoft Excel evaluates the operators from left to right. To change the order of evaluation, enclose the part of the formula to be calculated first in parentheses. Operator Description : (colon) Reference Operators , (comma) (single space) Negation (as in 1) % Percent ^ Exponentiation * and / Multiplication and division + and Addition and subtraction & Connects two strings of text (concatenation) = < > <= >= <> Comparison Regards, Tom Ogilvy Quote: >Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


Denny Campbel #5 / 9

Incorrect order of operations in formula
Hi Jonathan, Excel is using the order as shown in help under "calculating formulas, calculation order". Negation is figured before Exponentiation. I always use parentheses whether they're needed or not. For example I'd use =(2*5)+1 even though =2*5+1 would give the same result. I find it easier to read and less prone to errors. In your case you can force the order you want with = (5^2). I'll leave it for others to argue whether Excel's order of calculation is the "correct" order. HTH Denny Campbell Grand Rapids, MI USA
Quote: >I just noticed that Excel does not use correct order of operations >with  and ^. For example, the formula =  5 ^ 2 incorrectly displays >25 instead of 25. In other words, x^2 is interpreted as (x)^2, when >it should be interpreted as (x^2). >This is also a problem with multiplication; 1*5^2 also yields 25. >On the other hand, addition is fine; if it wasn't I'd be really worried. >So 05^2 yields 25. >If anyone can tell me why this makes sense in somebody's world, >I'd like to know. >Also, if the problem needs to be reported, how do I do it? >Thanks, >Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


Jonathan Bisho #6 / 9

Incorrect order of operations in formula
I realized after writing that my comparison with subtraction was flawed, and that the unary minus is a different operator than the binary minus. So, I didn't help by confusing the issue. but... FORTRAN seems to follow the convention I considered "correct;" a=5**2 print*,a yields 25.00000 C isn't a good reference since the power operator is a function The IDL (Integrated Data Language) yields 25 for print,5^2. So, Excel is the only "language" I regularly use that acts other than the way I see as intuitive. I would be interested in knowing why just for my edification. My reasons for thinking that it makes more sense to perform ^ before  is that a) that would be the normally understood order if I were writing formulas by hand, and b) it is more compact. I apologize for reopening this debate and for confusing the issue; I guess I didn't search for the right words on dejanews when I was looking to see if it had been discussed before. Thanks, Jonathan
Quote:
>Jonathan, >That is indeed the correct order of operation. Negation is the highest >(first) priority of standard arithmetic operations. Subtraction, which >uses the same symbol as negation, is lower priority than negation or >exponentiation, but is a completely different operation. >The two formulas >=5^2 >and >=05^2 >are NOT the same. In the first, the  is a negation operator. In the >second, it is a subtraction operator. >This is the way programming languages work. It is not a problem, and >does not need to be reported. >Cordially, >Chip Pearson >http://home.gvi.net/~cpearson/excel
>>I just noticed that Excel does not use correct order of operations >>with  and ^. For example, the formula =  5 ^ 2 incorrectly >displays >>25 instead of 25. In other words, x^2 is interpreted as (x)^2, >when >>it should be interpreted as (x^2). >>This is also a problem with multiplication; 1*5^2 also yields 25. >>On the other hand, addition is fine; if it wasn't I'd be really >worried. >>So 05^2 yields 25. >>If anyone can tell me why this makes sense in somebody's world, >>I'd like to know. >>Also, if the problem needs to be reported, how do I do it? >>Thanks, >>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


David J. Brade #7 / 9

Incorrect order of operations in formula
Actually, Jonathan, I sorta enjoyed it. As has been pointed out before in this group (I think it was this one, and I think Chip did the pointing), once you add VB or VBA to your set of languages you will get the behavior you expect: 5^2 => 25, as you also get with MatLab, Mathematica and SPlus. Excel is the only language I know of that gives higher precedence to the unary "" than to the power operator. But along the lines of what Alan suggested, I use parens liberally just for my own head's sake (it needs a lot of help). Regards to all, Dave Braden TMY Research Quote:
> I realized after writing that my comparison with subtraction was > flawed, and that the unary minus is a different operator than the > binary minus. So, I didn't help by confusing the issue. > but... > FORTRAN seems to follow the convention I considered "correct;" > a=5**2 > print*,a > yields 25.00000 > C isn't a good reference since the power operator is a function > The IDL (Integrated Data Language) yields 25 for > print,5^2. > So, Excel is the only "language" I regularly use that > acts other than the way I see as intuitive. I would be > interested in knowing why just for my edification. > My reasons for thinking that it makes more sense to > perform ^ before  is that a) that would be the > normally understood order if I were writing formulas > by hand, and b) it is more compact. > I apologize for reopening this debate and for > confusing the issue; I guess I didn't search for the > right words on dejanews when I was looking to see > if it had been discussed before. > Thanks, > Jonathan
> >Jonathan, > >That is indeed the correct order of operation. Negation is the highest > >(first) priority of standard arithmetic operations. Subtraction, which > >uses the same symbol as negation, is lower priority than negation or > >exponentiation, but is a completely different operation. > >The two formulas > >=5^2 > >and > >=05^2 > >are NOT the same. In the first, the  is a negation operator. In the > >second, it is a subtraction operator. > >This is the way programming languages work. It is not a problem, and > >does not need to be reported. > >Cordially, > >Chip Pearson > >http://home.gvi.net/~cpearson/excel
> >>I just noticed that Excel does not use correct order of operations > >>with  and ^. For example, the formula =  5 ^ 2 incorrectly > >displays > >>25 instead of 25. In other words, x^2 is interpreted as (x)^2, > >when > >>it should be interpreted as (x^2). > >>This is also a problem with multiplication; 1*5^2 also yields 25. > >>On the other hand, addition is fine; if it wasn't I'd be really > >worried. > >>So 05^2 yields 25. > >>If anyone can tell me why this makes sense in somebody's world, > >>I'd like to know. > >>Also, if the problem needs to be reported, how do I do it? > >>Thanks, > >>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


David J. Brade #8 / 9

Incorrect order of operations in formula
Actually, Jonathan, I sorta enjoyed it. As has been pointed out before in this group (I think it was this one, and I think Chip did the pointing), once you add VB or VBA to your set of languages you will get the behavior you expect: 5^2 => 25, as you also get with MatLab, Mathematica and SPlus. Excel is the only language I know of that gives higher precedence to the unary "" than to the power operator. But along the lines of what Alan suggested, I use parens liberally just for my own head's sake (it needs a lot of help). Regards to all, Dave Braden TMY Research Quote:
> I realized after writing that my comparison with subtraction was > flawed, and that the unary minus is a different operator than the > binary minus. So, I didn't help by confusing the issue. > but... > FORTRAN seems to follow the convention I considered "correct;" > a=5**2 > print*,a > yields 25.00000 > C isn't a good reference since the power operator is a function > The IDL (Integrated Data Language) yields 25 for > print,5^2. > So, Excel is the only "language" I regularly use that > acts other than the way I see as intuitive. I would be > interested in knowing why just for my edification. > My reasons for thinking that it makes more sense to > perform ^ before  is that a) that would be the > normally understood order if I were writing formulas > by hand, and b) it is more compact. > I apologize for reopening this debate and for > confusing the issue; I guess I didn't search for the > right words on dejanews when I was looking to see > if it had been discussed before. > Thanks, > Jonathan
> >Jonathan, > >That is indeed the correct order of operation. Negation is the highest > >(first) priority of standard arithmetic operations. Subtraction, which > >uses the same symbol as negation, is lower priority than negation or > >exponentiation, but is a completely different operation. > >The two formulas > >=5^2 > >and > >=05^2 > >are NOT the same. In the first, the  is a negation operator. In the > >second, it is a subtraction operator. > >This is the way programming languages work. It is not a problem, and > >does not need to be reported. > >Cordially, > >Chip Pearson > >http://home.gvi.net/~cpearson/excel
> >>I just noticed that Excel does not use correct order of operations > >>with  and ^. For example, the formula =  5 ^ 2 incorrectly > >displays > >>25 instead of 25. In other words, x^2 is interpreted as (x)^2, > >when > >>it should be interpreted as (x^2). > >>This is also a problem with multiplication; 1*5^2 also yields 25. > >>On the other hand, addition is fine; if it wasn't I'd be really > >worried. > >>So 05^2 yields 25. > >>If anyone can tell me why this makes sense in somebody's world, > >>I'd like to know. > >>Also, if the problem needs to be reported, how do I do it? > >>Thanks, > >>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


John Walkenbac #9 / 9

Incorrect order of operations in formula
This isssue is discussed at length in the comp.apps.spreadsheets FAQ, which is maintained by Russell Schulz and is available at: http://www.faqs.org/faqs/spreadsheets/faq/  Posted by John Walkenbach of JWalk & Associates   Visit "The Spreadsheet Page"   http://www.jwalk.com/ss 
Quote: >I just noticed that Excel does not use correct order of operations >with  and ^. For example, the formula =  5 ^ 2 incorrectly displays >25 instead of 25. In other words, x^2 is interpreted as (x)^2, when >it should be interpreted as (x^2). >This is also a problem with multiplication; 1*5^2 also yields 25. >On the other hand, addition is fine; if it wasn't I'd be really worried. >So 05^2 yields 25. >If anyone can tell me why this makes sense in somebody's world, >I'd like to know. >Also, if the problem needs to be reported, how do I do it? >Thanks, >Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT 


