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

    mooblie

    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. :confused:
     
  3. miles01110 macrumors Core

    miles01110

    Joined:
    Jul 24, 2006
    Location:
    The Ivory Tower (I'm not coming down)
    #3
    Excel's not the problem. Look at this page and see if this fixes your formula after you've made the necessary corrections to your formula.
     
  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
    A=Add
    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. :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/
     
  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

    Signal-11

    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.
     
  10. Readinglevelup macrumors member

    Readinglevelup

    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

    Signal-11

    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

    Signal-11

    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

    miles01110

    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

    sammich

    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
    In any case, google gives the right answer here.
     
  17. pit29 macrumors 6502a

    pit29

    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

    Signal-11

    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.
     

Share This Page