[Solved] Power of negative number

Discuss the spreadsheet application

[Solved] Power of negative number

Postby jorgetapia » Sat Nov 06, 2010 12:11 am

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
Last edited by Hagar Delest on Sun Nov 07, 2010 7:56 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.2. Windows XP professional SP3
jorgetapia
 
Posts: 2
Joined: Sat Nov 06, 2010 12:00 am

Re: Power of negative number

Postby Charlie Young » Sat Nov 06, 2010 12:55 am

jorgetapia 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


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 with

Code: Select all   Expand viewCollapse view
=-(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
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Power of negative number

Postby MrProgrammer » Sat Nov 06, 2010 5:13 am

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.
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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3872
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Power of negative number

Postby floris v » Sat Nov 06, 2010 12:14 pm

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).
AOO 4.1.6 op Linux Mint
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
User avatar
floris v
Moderator
 
Posts: 4168
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Power of negative number

Postby MrProgrammer » Sat Nov 06, 2010 9:41 pm

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))).
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).
User avatar
MrProgrammer
Moderator
 
Posts: 3872
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Power of negative number

Postby keme » Sun Nov 07, 2010 12:35 am

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).
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.)

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 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3268
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Power of negative number

Postby jorgetapia » Sun Nov 07, 2010 6:16 pm

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).
OpenOffice 3.2. Windows XP professional SP3
jorgetapia
 
Posts: 2
Joined: Sat Nov 06, 2010 12:00 am


Return to Calc

Who is online

Users browsing this forum: marty-0750 and 11 guests