Goal Seek with Complex Numbers

[monir]

Hello;

I'm trying to understand how complex numbers are handled/processed in Excel.
As related to my application, an interesting exercise would be to use Goal
Seek w/s command to find the roots of the equation:
X^2 + 4 = 0
setting the value (rounded) in cell A2 to 0 by changing A1
A1:: 1+i
A2:: =COMPLEX(ROUND(IMREAL(IMSUM(IMPOWER(A1,2),4)),6),
ROUND(IMAGINARY(IMSUM(IMPOWER(A1,2),4)),6))

Obviously a conventional/direct use of Goal Seek wouldn't work since Excel
treats complex numbers as text.

Perhaps, one should use Goal Seek twice in this case:
first: find the coefficient "a" for IMREAL(A2) = 0
second: find the coefficient "b" for IMAGINARY(A2) = 0
and the root would be "a+bi".

There might be an easier way to do it. Any suggetion ??
(Excel 2003 SP2, Windows XP)

Thank you kindly.

 

[monir]

Sorry!! My "Perhaps, ..." idea is incorrect.
My apologies!

Any suggestion ?? Thank you.

 

[monir]

Hi;

Here's one way to find the real and imaginary roots using
Solver.
(couldn't get Goal Seek working with complex numbers!!)

cell A1:: enter initial estimate of root real coefficient "a"
cell B1:: enter initial estimate of root imaginary coefficient "b"
cell C1:: complex root "a+bi"
.............=COMPEX(A1,B1)
cell A2:: evaluate equation at root C1
.............=IMSUM(IPOWER(C1,2),4) 'for equation X^2 + 4 =0
cell A3:: separate the real part
.............=IMREAL(A2)
cell B3:: separate the imaginary part
.............=IMAGINARY(A2)
Run Solver with:
target: A3=0
by changing: A1, B1
subject to constraint: B3=0

Numerical example:
Enter
A1:: 1
B1:: 1
Run Solver:
C1:: 2i

Now enter:
A1:: 1
B1:: -1
Run Solver:
C1:: -2i

The above simple procedure seems to be working fine and as desired for
finding the real and imaginary roots of any one-variant equation. I've
tested it successfully for up to quintic equations with real coefficients.

Any comments ??

Regards.
Monir