Goal Seek with Complex Numbers
 

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.

Goal Seek with Complex Numbers
 

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

Any suggestion ?? Thank you.


"monir" wrote:

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.

Goal Seek with Complex Numbers
 

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


"monir" wrote:

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

Any suggestion ?? Thank you.


"monir" wrote:

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.