Hi, and welcome to the forum.
Let's think about what happens when Calc evaluates
=(-2)^(1/3). First the expressions in parentheses are evaluated, so we have -2 and .3333333… then those numbers are passed to the exponentiation operator. Calc's documentation doesn't have many details about how exponentiation works, but I'm guessing that there are some special tests for cases X^0, X^1, X^2, and maybe the square root (X^.5), but in all other cases X^Y is evaluated as EXP(LN(X)*Y). So that would be EXP(LN(-2)*.3333333…). But the logarithm of a negative number is undefined, unless we allow complex numbers. So that's why (-2)^(1/3) produces #VALUE!. The 1/3 is evaluated before the exponentiation operator sees the result. There's no way that the operator "knows" that the exponent was evaluated as 1/3. But if
you know that you're taking the cube root of a negative number, move the minus sign outside of the exponentiation and evaluate it as
=-(2^(1/3)).
So, how does Excel make it work? Well that isn't documented, as far as I know, but I suspect that Excel's exponentiation operator also has special tests for X^Y where X is a negative number and Y is the reciprocal of an odd integer and it evaluates those as -((-X)^Y). But how far did they take this "special case" logic? I would guess Excel can evaluate (-2)^(1/5) and get -1.14870, but can it also evaluate (-2)^(3/5)? It would be the cube of -1.14870, that is, -1.51572. Or do we get #VALUE! or #NUM! for that case?
From a mathematical standpoint, we must use complex numbers to evaluate powers of negative numbers. What's (-1)^(1/2)? This is the square root of -1 and is the imaginary unit i (and its conjugate -i). Using complex numbers is good because we can still use the EXP(LN(X)*Y) method. But this gets a bit complicated because LN(X) is a multi-valued function in the complex domain. To five decimal places, we have:
LN(-2) = 0.69315+3.14159i or 0.69315+9.42478i or 0.69315+15.70796i
LN(-2)*.33333… = 0.23105+1.04720i or 0.23105+3.14159i or 0.23105+5.23599i
EXP(LN(-2)*.33333…) = 0.62996+1.09112i or -1.25992 or 0.62996-1.09112i
The cubes of
all three of the numbers in the last line are -2, and -1.25992 is no more correct than the other two.
0.62996+1.09112i × 0.62996+1.09112i = -0.79370+1.37473i then
-0.79370+1.37473i × 0.62996+1.09112i = -2.00000+0.00000i = -2
-1.25992+0.00000i × -1.25992+0.00000i = 1.58740+0.00000i then
1.58740+0.00000i × -1.25992+0.00000i = -2.00000+0.00000i = -2
0.62996-1.09112i × 0.62996-1.09112i = -0.79370-1.37473i then
-0.79370-1.37473i × 0.62996-1.09112i = -2.00000+0.00000i = -2
The cube root formulas are:
=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;0*PI()));1/3))=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;2*PI()));1/3))=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;4*PI()));1/3))We always have three cube roots of a number, however at least two of them are complex.
Using complex numbers allows us to evaluate (-2)^(1/2) just as easily. It's 1.41421i or -1.41421i. The formulas are:
=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;0*PI()));0.5))=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;2*PI()));0.5))There are of course always two square roots of a number, and one is the negative of the other.
And for (-2)^(3/5) we have the five values (the middle one is -1.51572):
=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;0*PI()));3/5))=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;2*PI()));3/5))=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;4*PI()));3/5))=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;6*PI()));3/5))=IMEXP(IMPRODUCT(IMSUM(IMLN(-2);COMPLEX(0;10*PI()));3/5))In summary, for X^Y with X<0:
- if Y is a small integer use multiplication
- if Y is an even multiple of the reciprocal of an odd integer evaluate =(-X)^Y to get the non-complex value
- if Y is an odd multiple of the reciprocal of an odd integer evaluate =-((-X)^Y) to get the non-complex value
- in all other cases use complex numbers
Edit: Corrected formulas in summary |
Edit: Corrected a few other formula errors |
If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6. The locale for any menus or Calc formulas in my posts is English (USA).