complex numbers with excel

[Andrew]

Hello,
I am trying to find the complex roots of a polynomial using Excel. If
I have the polynomial s^2-5s+1000, I should get the roots to be 31.6
angle 85.4 and 31.6 angle -85.4. How do I do this calculation in
excel?

thanks

 

[joel]

y= (-b+/-sqrt(b^2-4ac))/2a

y=(5+/-sqrt(5^2-4(1000))/2

y=2.5+31.52i
and
y=2.5-31.52i


the angle in radians is
atan(31.42/2.5)

convert radians to angles mult by 180/pi = 85.46

the length = sqrt((31.42^2)+(2.5^2)) = 31.62


follow same steps for 2nd root.

 

[jasontferrell]

I'm a little rusty on complex numbers, so I'm not getting what you say
the answer should be, but here's what I did. I separated the formula
into imaginary and real parts. Taking the complex portion to be a+bi,
I came up with:
(a+bi)*(a+bi)-5(a+bi)+1000
=
a^2+abi+abi+bi^2-5a-5bi+1000
=
a^2-b^2-5a+1000 (real)
2abi-5bi (imaginary)
Next, I put the last two formulas into individual cells. Let's say a
is in A6, b is in B6, the real formula is A11, and the imaginary
formula is B11. I ran solver to set cell A11 equal to 0 subject to
the constraint of B11 also being 0, by changing cells A6:B6.
However, I came up with a=2.5 and b=31.5238
I think I am doing something wrong, because I cannot get two
solutions, but does this technique of separating real and imaginary
and using solver help you?

 

[Dana DeLouis]

Hello,
I am trying to find the complex roots of a polynomial using Excel. If
I have the polynomial s^2-5s+1000, I should get the roots to be 31.6
angle 85.4 and 31.6 angle -85.4. How do I do this calculation in
excel?

Hi. One of a few ways...
Assume this equation in B1, with b= -5, and k = +1000

'complex solution...
=IMDIV(IMSUB(IMSQRT(b^2-4*k),b),2)

' Magnitude
=IMABS(B1)

'Angle (other angle is opposite sign)
=DEGREES(ATAN2(IMREAL(B1),IMAGINARY(B1)))

Returns:

2.5+31.5238005322962i
31.6227766
85.46563923

= = =
HTH
Dana DeLouis

 

[Andy]

Hi.  One of a few ways...
Assume this equation in B1, with b= -5, and k = +1000

'complex solution...
=IMDIV(IMSUB(IMSQRT(b^2-4*k),b),2)

' Magnitude
=IMABS(B1)

'Angle  (other angle is opposite sign)
=DEGREES(ATAN2(IMREAL(B1),IMAGINARY(B1)))

Returns:

2.5+31.5238005322962i
31.6227766
85.46563923

= = =
HTH
Dana DeLouis

Thanks for your help.