# A possible calculation glitch on Excel?

Discussion in 'Mac Apps and Mac App Store' started by shomuban, Dec 13, 2009.

1. ### shomuban macrumors newbie

Joined:
Dec 13, 2009
#1
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!

2. ### mooblie macrumors 6502

Joined:
Apr 23, 2009
Location:
The Highlands, Scotland
#2
Don't see the problem. The "power" operator takes precedence, and Excel is correct in both cases.

3. ### miles01110 macrumors Core

Joined:
Jul 24, 2006
Location:
The Ivory Tower (I'm not coming down)
#3

4. ### Guiyon macrumors 6502a

Joined:
Mar 19, 2008
Location:
North Shore, MA
#4
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

5. ### lostless macrumors 6502

Joined:
Oct 22, 2005
#5
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
S=subtract.

6. ### DKatri macrumors 6502

Joined:
Sep 2, 2009
Location:
Birmingham, UK
#6
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.

7. ### music10man macrumors newbie

Joined:
Mar 1, 2010
#7
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. 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/

8. ### Buzz Bumble Guest

Joined:
Oct 19, 2008
Location:
New Zealand
#8
"-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.

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.

9. ### Signal-11 macrumors 65816

Joined:
Mar 23, 2008
Location:
2nd Star to the Right
#9
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.

Joined:
Mar 2, 2010
Location:
North America
#10
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

11. ### Signal-11 macrumors 65816

Joined:
Mar 23, 2008
Location:
2nd Star to the Right
#11
LOL, you're absolutely right. Got me there.

12. ### Buzz Bumble Guest

Joined:
Oct 19, 2008
Location:
New Zealand
#12
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.

13. ### Signal-11 macrumors 65816

Joined:
Mar 23, 2008
Location:
2nd Star to the Right
#13
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.

14. ### Buzz Bumble Guest

Joined:
Oct 19, 2008
Location:
New Zealand
#14
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).

15. ### miles01110 macrumors Core

Joined:
Jul 24, 2006
Location:
The Ivory Tower (I'm not coming down)
#15
Honestly the above statement is a little ambiguous.

Code:
```-2^2 = (-1) (2^2) = (-1) (4) = -4
```
Exactly, but that's not what the issue is.

No, (-2)^2 is correct.

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.

16. ### sammich macrumors 601

Joined:
Sep 26, 2006
Location:
Sarcasmville.
#16
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`

17. ### pit29 macrumors 6502a

Joined:
May 23, 2006
Location:
The Golden State
#17
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.

18. ### Signal-11 macrumors 65816

Joined:
Mar 23, 2008
Location:
2nd Star to the Right
#18
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.