My problem is XX^(1/3) an xx<0. My desktop calculator and Excel working without problem. But Calc send my : #Value.
Try (-2)^(1/3), the result is -1.26.
¿how is the form in calc to obtain the result?
Thanks
[Solved] Power of negative number
-
- Posts: 2
- Joined: Sat Nov 06, 2010 12:00 am
[Solved] Power of negative number
Last edited by Hagar Delest on Sun Nov 07, 2010 7:56 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 3.2. Windows XP professional SP3
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Power of negative number
I have noticed some precedence weirdness with Calc in the past (try doing a Normal pdf using SQRT and EXP, for example), but this particular problem is solved withjorgetapia wrote:My problem is XX^(1/3) an xx<0. My desktop calculator and Excel working without problem. But Calc send my : #Value.
Try (-2)^(1/3), the result is -1.26.
¿how is the form in calc to obtain the result?
Thanks
Code: Select all
=-(2^(1/3))
Edit: However, it should be noted that in Calc, =-(2^(1/2)) = -1.4142135624, and in Excel -2^(1/2) produces #NUM. |
Apache OpenOffice 4.1.1
Windows XP
Windows XP
- MrProgrammer
- Moderator
- Posts: 4903
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Power of negative number
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:
=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 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.
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.09112iThe 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 = -2The 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 |
Last edited by MrProgrammer on Sun Sep 28, 2014 4:41 am, edited 4 times in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Power of negative number
It's a bug. Calc should not interpret -2^(1/3) as (-2)^(1/3) but as -(2^(1/3)), just as it shouldn't interpret -3^(1/2) as (-2)^(1/2), which it does. It's a matter of precedence. Exponentiation takes precedence over multiplication, that is: it's carried out earlier, and -2^(1/3) should be read as -1*2^(1/3).
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
- MrProgrammer
- Moderator
- Posts: 4903
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Power of negative number
The precedence for Calc's operators is apparently a "misfeature" inherited from Excel: Spreadsheet should not implement all of Excel's order of precedence for worksheet operators. So it's working as designed. The issue is classified as a FEATURE not as a DEFECT. As of today it has six votes. The current plan suggests "adding the appropriate conversions into the Microsoft formats input/output converter and implementing the mathematically correct precedence rule in OpenOffice.Org starting from the next major version". But whether OOo changes the precedence rules or not, you will need to be careful with using a negative value for the base in an exponentiation operator.
If the intent is to obtain the real-valued cube root, use =IF({value}>=0;{value}^(1/3);-((-{value})^(1/3))).
If the intent is to obtain the real-valued cube root, use =IF({value}>=0;{value}^(1/3);-((-{value})^(1/3))).
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Power of negative number
It's a matter of some dispute. However, most programming languages that have operator precedence agree with most mathemathical references: unary operators have higher precedence than binary operators. The minus in the given examples is clearly a unary operator. (Some expressions may have ambiguity concerning whether operators are unary or binary, but the above is unambiguous.)floris v wrote:It's a bug. Calc should not interpret -2^(1/3) as (-2)^(1/3) but as -(2^(1/3)), just as it shouldn't interpret -3^(1/2) as (-2)^(1/2), which it does. It's a matter of precedence. Exponentiation takes precedence over multiplication, that is: it's carried out earlier, and -2^(1/3) should be read as -1*2^(1/3).
Still, raising a negative number to the power of 1/3 is mathemathically possible in the world of real numbers. The reason why most software applications don't do it is that they use a general power calculation (which will not raise negative numbers to non-integer powers unless you use complex math, and they do not take special care of the cases when the exponent is a fraction with odd denominator). You have to take care of it yourself, moving the minus out of harm's way, as it were.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
-
- Posts: 2
- Joined: Sat Nov 06, 2010 12:00 am
Re: Power of negative number
Thanks all for your suggestions. Is very important understand how work calc to void errors in our results.
Mi problem is XX maybe any number because is result of: zz-root(vv). So the funtion is (zz-root(vv))^(1/3)
I take the last suggest of MrProgrammer and change to: =IF({value}>=0;1;-1)*(ABS(value))^(1/3).
Mi problem is XX maybe any number because is result of: zz-root(vv). So the funtion is (zz-root(vv))^(1/3)
I take the last suggest of MrProgrammer and change to: =IF({value}>=0;1;-1)*(ABS(value))^(1/3).
OpenOffice 3.2. Windows XP professional SP3