Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

shomuban

macrumors newbie
Original poster
Dec 13, 2009
1
0
I was shocked to find what might be a calculation error that showed up on my Excel spreadsheet. I am running Mac OS X 10.5.8 on a MacBook PRo and using Excel 12.2.3 and I would appreciate knowing if anyone else can duplicate this.

I have two cells named 'aa' and 'ff' and I have another cell where I have placed the formula '=-ff^2+aa'. When the value of aa is 4 and the value of ff is 1, Excel gives me '5' as the result. When I change the formula to '=aa-ff^2', it gives me the correct answer of 3. What's going on here?? It's hard to believe that Excel would be prone to such a basic error after all this time!
 
I would assume Excel is doing this:
Code:
((-ff)^2) + aa
((-1)^2) + 4
1+4 == 5

Which is the correct answer for that order of operations
 
No, it's doing math correct. Remember algebra rules of operation. you have negative 1 squared which is 1, and then add 4, which is 5. It looks like this -1^2+4=5. Exponents always go first before adding or subtracting in algebra. Try it in a scientific calculator. The other formula is 4-1^2. So you do the exponent first which 1^2 = 1 and then subtract it from 4 which is 3. PEMDAS is your friend in algebra
P=parenthesis
E=exponents
M=multiply
D=divide
A=Add
S=subtract.
 
I was shocked to find what might be a calculation error that showed up on my Excel spreadsheet. I am running Mac OS X 10.5.8 on a MacBook PRo and using Excel 12.2.3 and I would appreciate knowing if anyone else can duplicate this.

I have two cells named 'aa' and 'ff' and I have another cell where I have placed the formula '=-ff^2+aa'. When the value of aa is 4 and the value of ff is 1, Excel gives me '5' as the result. When I change the formula to '=aa-ff^2', it gives me the correct answer of 3. What's going on here?? It's hard to believe that Excel would be prone to such a basic error after all this time!

The problem is you are a little confused. if you square a minus number (ie -1) it always gives a positive number so, once ff has been squared it gives 1 not minus 1 it might work if you used '=-(ff^2)+aa'

EDIT: beaten to it.
 
Au contrair

Actually, order of operations suggests the opposite of what most everyone is saying. Making a number negative is the same as multiplying by -1, and yes, exponents take preference. So, you must raise by the exponent before multiplying by -1. Ex. -2^2 = -(2^2), NOT (-2)^2 (as this would violate order of operations). If you don't believe me, type it into a graphing calculator and see what you get. :D Microsoft has a Knowledge Base article about this because so many people have run into this same problem. They decided to make the negation operator have precedence over any other operator for some reason (Which is obviously against standard order of operations). I spent an hour pouring over a massive engineering spreadsheet that was acting weird before I realized what was going on. Since looking it up, I've seen a lot of people misquoting their "gradeschool math teacher".

http://support.microsoft.com/kb/q132686/
 
"-2" is a number in its own right. It has nothing to do with "multiplying by -1" nor "subtracting from 0". -2^2 is (-2)^2 ... always has been, always will be.


Microsoft has a Knowledge Base article about this because so many people have run into this same problem. They decided to make the negation operator have precedence over any other operator for some reason (Which is obviously against standard order of operations).

Typical Microsoft ... make up their own rules as they go along and then expect everyone else to follow. The idiots at Microsoft don't have any right to go around changing the basic rules of mathematics to suit themselves. :(
 
Buzz Bumble is correct.

-2^2 = 4
-(2^2) is a different operation.

This is VERY basic arithmetic. The problem is the state of education where people don't understand the basic properties of numbers.
 
Buzz Bumble is correct.

-2^2 = 4
-(2^2) is a different operation.

This is VERY basic arithmetic. The problem is the state of education where people don't understand the basic properties of numbers.

Kind of a nitpick, but I just wanted to say those two are the same operations. The different one would be (-2)^2=4. Sorry, I couldn't help myself. haha
 
Kind of a nitpick, but I just wanted to say those two are the same operations. The different one would be (-2)^2=4. Sorry, I couldn't help myself. haha

Errr ... I'm not sure which "two" you're referring to, but the two examples given by Signal-11 are not the same - that's the point.

-2^2 or (-2)^2 both give an answer of 4.

-(2^2) is a different calculation giving an answer of -4.
 
Errr ... I'm not sure which "two" you're referring to, but the two examples given by Signal-11 are not the same - that's the point.

-2^2 or (-2)^2 both give an answer of 4.

-(2^2) is a different calculation giving an answer of -4.

Actually, he's correct. -2^2=-4. The - applies to the term (2^2).

What it comes down to is the ambiguity in determining whether or not -2^2 is -a^b or a^b where a = -2.

As written, -2^2 = -(2^2), in the form of -a^b not (-2)^2.

You agreed with this yourself - "Typical Microsoft ... make up their own rules as they go along and then expect everyone else to follow. The idiots at Microsoft don't have any right to go around changing the basic rules of mathematics to suit themselves." Excel evaluates this as a^b.
 
Actually, he's correct. -2^2=-4. The - applies to the term (2^2).

Nope. -2^2 = 4.

"-2" is a number in its own right, not an operation.

"-(2)" is an operation.

If you want an answer of 4, then -2^2 is correct.
If you want an answer of -4, then -(2^2) is correct, as is -(-2^2).

People don't go around writing (-3)+6, they just write -3+6, because -3 is a number. The answer is of course 3, not -9, which of course be -(3+6).
 
Nope. -2^2 = 4.

"-2" is a number in its own right, not an operation.

Honestly the above statement is a little ambiguous.

Code:
-2^2 = (-1) (2^2) = (-1) (4) = -4

"-(2)" is an operation.

Exactly, but that's not what the issue is.

If you want an answer of 4, then -2^2 is correct.

No, (-2)^2 is correct.

People don't go around writing (-3)+6, they just write -3+6, because -3 is a number. The answer is of course 3, not -9, which of course be -(3+6).

The implications of parenthesis and grouping are considerably more significant when multiplying, dividing, or raising to a power than they are with addition or subtraction. Your simplistic example clearly doesn't do the issue justice.
 
As Buzz Bumble is saying:

-2^2 = 4. No two ways about it.

That is a negative sign, not a subtract/minus sign. The negative sign belongs to the number directly to the right of it, first (unless parenthesis define otherwise). That gives it the distinction between those two 'dashes'.

Hence:

Code:
1--2^2 = -3

In any case, google gives the right answer here.
 
There's probably still a difference between -2 and -x, where x is a variable that can assume both positive and negative values.

So it seems that -2^2 is (-2)^2 = 4, and -x^2 is indeed -(x^2). x can be 2 or -2.

I don't see any problem here.
 
As Buzz Bumble is saying:

-2^2 = 4. No two ways about it.

That is a negative sign, not a subtract/minus sign. The negative sign belongs to the number directly to the right of it, first (unless parenthesis define otherwise). That gives it the distinction between those two 'dashes'.

Hence:

Code:
1--2^2 = -3

In any case, google gives the right answer here.

Are you sure? Because Google Calculator very clearly gives the answer 5, not -3 for the 1--2^2. Note, if we're going to use Google as a reference, -2^2 returns -4.

The issue is not whether or not the "-" is a sign or an operator. It's how it should be parsed, as written.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.