Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Excel calculation order

    The math problem: (-1/16)^(2/3) produces an error in Excel.

    However, ((-1/16)^2)^(1/3) does not.
    Neither does: ((-1/16)^(1/3))^2

    Why is that?

    Guess it's that -1/16 with the attempt to raise it to the 2/3 power or the positive 0.6666 power.
    Those negatives just have a problem when intermixed in power problems.
    Almost gives the impression the problem doesn't have a real solution, but it does actually.
    Last edited by kweaver; 2011-05-15 at 21:12.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I presume that the method for calculating whole number roots are different than fractional. I will make a leap and presume the fractional problem:
    A=(-1/16)^(2/3)
    A=-0.0625^0.66666666666666
    lnA = ln(-0.0625^0.66666666666666)
    lnA = 0.66666666666666 * ln(-0.0625)
    ln(-0.625) is undefined so is an error.....

    I presume the 1/3 uses a different procedure (perhaps a built-in one) involving some type of series...

    These are only speculations, I am not privy to the actual coding done in excel...
    Steve
    PS something to think about:
    (-1/16)^(1/2)

    Is a complex (non-real) number.

    But using distribution manipulation like you do, excel can calculate it:
    ((-1/16)^(2))^(1/4) = 0.25

    But 0.25^2 does not equal -1/16...

    Which indicates to me that your distributive recalculation is not always the same thing as the original. Your reordering may be changing the values....

    The solution you get in those 2 calcs:
    0.15749 ^ (3/2) = 1/16 not -1/16....
    Last edited by sdckapr; 2011-05-16 at 11:33.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts

    Excel Order of Operations

    Excel has a very definite order of operations as shown in the attached table. The best policy is to never depend on the default order but to control the order of operations using parenthesis with the inner most set of parenthesis being evaluated first and working outward.

    The default order of operations is as shown in the table with operations at the same level being evaluated left to right.

    Examples:
    Where A1=1; A2=2; A3=3; A4=4

    =A1-(A2*(A3^A4)) This is the parenthesized version of the default order.
    =1-(2*(3^4))
    =1-(2*81)
    =1-162
    =-161

    =(A3*A4)-(A1*(A4^A2))
    =(3*4)-(1*(4^2))
    =12-(1*(16)
    =12-16
    =-4

    =(A3*A4)-((-A1)*(A4^A2))
    =(3*4)-((-1)*(4^2)
    =12-(-1*16)
    =12-(-16)
    =28
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-05-17 at 09:52. Reason: Fixed Type pointed out by Fred...Thanks
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    typo?

    RetiredGeek

    I think you have a typo in your first example where you do 3^4 and in the next line have 2*91. The following line does correctly show 162, which would be the result of 2*81 and 81=3^4.

    But for any non-math people reading your post (eg, my students), you might want to consider correcting that. Otherwise, I know I'll be getting answers like that.

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    there are a few other quirks that one can show and they have nothing to do with Excel. These have to do when neg numbers are introduced where they shouldn't be.

    For example: log bases are defined to be non-neg - the log bases correspond to the base of an exponential. That is log(base) of arg = exp where base is a non-neg number by definition; when considering the corresponding exponential representation, this becomes base^exp = arg where base is >0 and NE 1. This is because if base were allowed to be neg, then you'd have an oscillating function as the sign of the result alternates between plus and minus as exp changes between even and odd. Without this restriction, one could ask the question log(-2)16 = ?. My students always say 4 since (-2)^4 = 16 (a teacher's favorite trick log question).

    Similarly log args are also non-neg as you observed in Steve's example, since a pos base raised to any exp is always pos.

    Makes for nice continuous functions.

    Another item using the property of exponents in fraction form that applies to the original example: sqrt(4^2) = (sqrt(4))^2. But if use -4, one gets sqrt(-4^2) =4 which is NE to (sqrt(-4))^2 = -4. Hence, the rules of exponents do not apply to neg numbers.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by fburg View Post
    RetiredGeek

    I think you have a typo in your first example where you do 3^4 and in the next line have 2*91. The following line does correctly show 162, which would be the result of 2*81 and 81=3^4.

    But for any non-math people reading your post (eg, my students), you might want to consider correcting that. Otherwise, I know I'll be getting answers like that.

    Fred
    Fred,

    Thanks, those fat old fingers and bad eyes.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •