Difficulty with IMPOWER() Worksheet Function
monir
1) The cubic root of complex number "x+yi" is calculated using IMPOWER().
The w/s function appears to return incorrect values when the real
coefficient "x" is -ve.
2) For example:
A1:: -8+0i
B1::= IMPOWER(A1,1/3)
the function returns: 1. + 1.7320508i
instead of the correct value of -2.
3) The problem appears to be consistent with calculating theta [=atan(y/x)]
as "pi" instead of "0" for the above example "-8+0i"
How to fix the problem ?? According to Excel Help, the w/s function
IMPOWER() should work correctly regardless of the complex coefficients in A1
??
Thank you kindly.
Gary''s Student
even though
1+1.73205080756888i
does not "look like"
-2
Say in C1 we enter:
=IMPOWER(B1,3)
we see displayed:
-8.00000000000003-2.03365969897452E-014i
this means the answer is really "close enough" within roundoff error.
--
Gary''s Student - gsnu200790
Gary''s Student
http://en.wikipedia.org/wiki/Cube_root
--
Gary''s Student - gsnu200790
Dana DeLouis
> as "pi" instead of "0" for the above example "-8+0i"
=ATAN2(-8,0)
I believe your answer ( 1. + 1.73i ) is correct, as most math programs return the same principal root (as mentioned in Gary's article)
In Excel,that would be...
=IMEXP(IMPRODUCT(IMLN(-8),1/3))
=IMEXP(IMDIV(IMLN(-8),3))
I have a question...
=POWER(-8,1/3)
returns -2 in Excel 2007.
Is this a change? I thought this gave an error in prior versions ??? Does anyone remember?
Although correct in a sense, math program would return the principal root ..ie..
Power[-8., 1/3]
1.+ 1.73205 I
--
Dana DeLouis
"monir" <mo...@discussions.microsoft.com> wrote in message news:6559AFF8-2C29-477D...@microsoft.com...
Gary''s Student
--
Gary''s Student - gsnu200790
Bernard Liengme
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Gary''s Student" <GarysS...@discussions.microsoft.com> wrote in message
news:CD7F99FB-77BC-4A5C...@microsoft.com...
Harlan Grove
...
>A1:: -8+0i
>B1::= IMPOWER(A1,1/3)
>the function returns: 1. + 1.7320508i
>instead of the correct value of -2.
There are 3 cube roots of EVERY complex number. For real numbers,
there's always a real cube root AND 2 conjugate complex cube roots.
This is one of the latter, and 1-1.7320508i is the other. IOW, IMPOWER
*IS* returning a correct result, it's just that there's more than 1
correct result (as can also happen with IRR).
More generally, for every positive odd integer n there are n DISTINCT
roots of any nonzero complex number, and AT MOST ONE of those roots
would be real. All the others would be pairs of conjugate complex
numbers.
>3) The problem appears to be consistent with calculating theta [=atan(y/x)]
>as "pi" instead of "0" for the above example "-8+0i"
...
An old FORTRAN problem!
It's usually best to calculate odd integer roots of negative reals as
-((-(negative real))^(1/odd integer))
A1: -8+0i
B1:
=IF(IMREAL(A1)>=0,IMPOWER(A1,1/3),IMPRODUCT(IMPOWER(IMPRODUCT(A1,-1),
1/3),-1))
B1 returns -2. IMO, it should return -2+0i, but that's formatting.
monir
1) One would expect the w/s function IMPOWER("x+yi",1/3) to return the
principal value, if any, similar to:
....=IMSQRT("-4+0i") correctly returning +2i and not -2i
....=IMPOWER("8+0i",1/3) correctly returning +2.0, and not -1+sqrt(3)i or
-1-sqrt(3)i
2) Hence, IMPOWER("-8+0i",1/3) should return -2.0 and not one of the
conjugate pair 1+sqrt(3)i or 1-sqrt(3)i.
3) My experience with complex numbers in XL environment is rather limited.
However, one might reasonably argue that a complex number with zero imaginary
coefficient is equivalent to a real number!
Now try =POWER(-8,1/3). You would correctly get -2.0 and not #NUM!
4) IMPOWER() actually relies on ATAN2(x,y) and not ATAN(y/x) to convert
complex numbers to polar, contrary to the XL Help info on the function. This
together with the always non-negative "r" maybe an internally-wired factor
determining which value is returned by IMPOWER() and similar complex number
functions.
(Excel 2003 SP2, Win XP)
Regards.
"Harlan Grove" wrote:
> monir <mo...@discussions.microsoft.com> wrote...
> ....
> >A1:: -8+0i
> >B1::= IMPOWER(A1,1/3)
> >the function returns: 1. + 1.7320508i
> >instead of the correct value of -2.
>
> There are 3 cube roots of EVERY complex number. For real numbers,
> there's always a real cube root AND 2 conjugate complex cube roots.
> This is one of the latter, and 1-1.7320508i is the other. IOW, IMPOWER
> *IS* returning a correct result, it's just that there's more than 1
> correct result (as can also happen with IRR).
>
> More generally, for every positive odd integer n there are n DISTINCT
> roots of any nonzero complex number, and AT MOST ONE of those roots
> would be real. All the others would be pairs of conjugate complex
> numbers.
>
> >3) The problem appears to be consistent with calculating theta [=atan(y/x)]
> >as "pi" instead of "0" for the above example "-8+0i"
> ....
Harlan Grove
>Thank you all for your thoughtful replies. Here're some comments:
>
>1) One would expect the w/s function IMPOWER("x+yi",1/3) to return the
>principal value, if any, similar to:
>....=IMSQRT("-4+0i") correctly returning +2i and not -2i
>....=IMPOWER("8+0i",1/3) correctly returning +2.0, and not -1+sqrt(3)i or
>-1-sqrt(3)i
From the World of Mathematics: 'Informally, the term "principal root"
is often used to refer to the root of unity having smallest positive
complex argument.' In this sense, Excel's IMPOWER *does* return the
principal root.
>2) Hence, IMPOWER("-8+0i",1/3) should return -2.0 and not one of the
>conjugate pair 1+sqrt(3)i or 1-sqrt(3)i.
'Hence' based on a faulty (in this case, faulty semantics) conditions
leads to vacuous conclusions. Notationally, A => B, if A is false the
statement is true no matter whether B is true or false.
>3) My experience with complex numbers in XL environment is rather limited.
>However, one might reasonably argue that a complex number with zero imaginary
>coefficient is equivalent to a real number!
Yup. So?
>Now try =POWER(-8,1/3). You would correctly get -2.0 and not #NUM!
Again, so? *All* reals have real odd order roots. If you want real
roots for real numbers, use POWER, not IMPOWER.
>4) IMPOWER() actually relies on ATAN2(x,y) and not ATAN(y/x) to convert
>complex numbers to polar, contrary to the XL Help info on the function.
...
So? ATAN2 and equivalents in other programming languages are
**ALWAYS** preferable to ATAN. Anyway, online help for IMPOWER doesn't
show ATAN, it shows a symbolic inverse tangent. Not unambiguous, but
not necessarily an error.
>This together with the always non-negative "r" maybe an internally-wired
>factor determining which value is returned by IMPOWER() and similar complex
>number functions.
If you're talking about polar coordinates, r is necessarily always
nonnegative BY DEFINITION. Norms always give nonnegative real results.
And the principle nth root is always based on the principal argument
in the interval [0, 2 pi) and dividing it by n. That ALWAYS returns
the root in the 1st quadrant of the complex plane with the smallest
positive argument, so the principal root as informally defined above.
In this case Microsoft is following generally accepted mathematical
conventions. Adapt!