Goal Seek with Complex Numbers

monirg

monirg

Well-known Member
Joined
Jan 11, 2005
Messages
629
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 (rounded) value in cell A2 to 0 by changing A1
Code: 
A1:: 1+i
A2:: =COMPLEX(ROUND(IMREAL(IMSUM(IMPOWER(A1,2),4)),6),
              ROUND(IMAGINARY(IMSUM(IMPOWER(A1,2),4)),6))
Obviously a conventional or 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 thought ??

Thank you kindly.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Sorry!! My "Perhaps, ..." idea is incorrect.
My apologies!

Any suggestion ?? Thank you.
 
Upvote 0
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
 
Last edited:
Upvote 0
Hi Monir

I haven't tested it but it does seem a good way to get 1 root of the polynomial.


After your last post I was thinking about writting a UDF to determine all the roots of a polynomial

- you can use the solver to get the roots by trying different guesses but it may not be easy sometimes

- you can use the solver to get one root, as you did, then extract it from the polynomial and get a polynomial one degree lower. Then you'd just repeat the procedure. This could be automated

- the one I prefer is to implement one of those methods derived from the Newton-Raphson that work with an array of guesses and will result in one array with all the roots. It has been a while since the numerical analysis classes but I'll try to do it during the next one or two weeks (if I have the time).

Cheers
 
Upvote 0
Hi PGC;

1) Very interesting idea and you certainly have the expertise and the knowledge to write an efficient general UDF to do it.
I may have one or two things to post shortly on the subject matter. Possibly in the next couple of days and hopefully before you actually start working on it.

2) Meanwhile, let me go back to my earlier simple procedure for finding the real and imaginary roots using Solver.
There appears to be some difficulties possibly with XL floating point precision which seriously contribute to the Solver solution.

3) Let us consider the following equation as a demo:
x^3 + p x^2 + q x + r = 0
where:
p = 660.96866353
q = - 78862.11028966
r = - 80525640.7629787
The above cubic equation has the following roots:
...x1 = 327.992927017581
...x2 =-494.480795275332 + 31.6075878056574i
...x3 =-494.480795275332 - 31.6075878056574i

4) On a worksheet:
cell B33:: enter poly coefficient "p"
cell B34:: enter poly coefficient "q"
cell B35:: enter poly coefficient "r"

cell K31:: enter initial estimate of root real coefficient "a"
cell L31:: enter initial estimate of root imaginary coefficient "b"
cell M31:: complex root "a+bi"
Code: 
=COMPLEX(ROUND(K31,8),ROUND(L31,8))
cell K32:: evaluate equation at root M31
Code: 
=IMSUM(IMPOWER(M31,3),IMPRODUCT(IMPOWER(M31,2),B33),IMPRODUCT(M31,B34),B35)
cell K34:: separate the real part
Code: 
=ROUND(IMREAL(K32),7)
cell L34:: separate the imaginary part
Code: 
=ROUND(IMAGINARY(K32),7)

5) Solver add-in module:
target: K34=0
by changing: K31, L31
subject to constraint: L34=0
Solver Options:
...precision = 1.E-7
...convergence = 1.E-7
...use automatic scaling
...estimates = Quadratic
...derivatives = Central
...search = Conjugate

6) Numerical example:
Enter the following values:
K31:: 320
L31:: 1

7) Now run Solver:
"Solver found a solution. All constraints and optimality conditions are satisfied."
"Keep Solver Solution". "OK"

Root M31:: 327.99292701
Target K34:: - 0.0051362
Constraint L34:: 0

8) Notice that the target was never met, contrary to the Solver message. Not even close!!
Changing the Solver Options in Item 5. above improved nothing. Also, axis translation along the x-axis to have the new Origin close to the root didn't solve the problem!

Would very much appreciate your expert advice on how to fix the problem.

Thank you kindly.
 
Upvote 0
Hi Monir

I didn't yet have the time yet to analyse your post, but I will.

Reading your post reminded me of a solution for the cubic equation using a closed formula. I did the code long ago, but then some years ago I translated it to vba. I think I never posted it before.

It's just a curiosity, but you can try it, and maybe have some use for it. I just tried it with your example.

For your example, write the coefficients in B2:B5

B2: 1
B3: 660.9686635
B4: -78862.11029
B5: -80525640.76

Now select E2:F4, and enter in the formula bar:

=CubicEq(B2,B3,B4,B5)
and confirm with CTRL-SHIFT-ENTER.

You'll get the 3 roots.

Notice that you'll always get the usual approximation errors of the floating point operations and so instead of a zero you may get something like 1E-15.

<TABLE style="BORDER-TOP-WIDTH: 2px; BORDER-LEFT-WIDTH: 2px; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BACKGROUND: #fff; BORDER-BOTTOM-WIDTH: 2px; BORDER-BOTTOM-COLOR: #cccccc; BORDER-TOP-COLOR: #cccccc; FONT-FAMILY: Arial,Arial; BORDER-COLLAPSE: collapse; BORDER-RIGHT-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc" cellPadding=1 border=1><TBODY><TR><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">A</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">B</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888" width=30>C</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">D</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">E</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">F</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888" width=30>G</TH></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">1</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Coefficients</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Values</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Real</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Imaginary</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">2</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">a</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">1</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Result 1</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">-494.4807953</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">31.60759</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">3</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">b</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">660.9686635</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Result 2</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">-494.4807953</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">-31.6076</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">4</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">c</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">-78862.11029</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Result 3</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">327.992927</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">-8.9E-15</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">5</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">d</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">-80525640.76</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">6</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=8>[Book1]Sheet1</TD></TR></TBODY></TABLE>

And here's the code:

Code: 
' PGC - UDF to solve a cubic equation with real coefficients
' Equation - a*x^3+b*x^2+c*x+d=0
' Returns a 3 rows * 2 columns array with the three solutions
' To use in the worksheets:
' - select a 3 rows * 2 columns range (ex. D1:E3)
' - in the formula bar enter =CubicEq(a,b,c,d) and confirm with CTRL-SHIFT-ENTER
Function CubicEq(a As Double, b As Double, c As Double, d As Double)
Dim p As Double, q As Double, disc As Double
Dim u(1 To 2) As Double, uc(1 To 2) As Double, i As Integer, arrResult(1 To 3, 1 To 2) As Double
 
p = -b ^ 2 / 3 / a ^ 2 + c / a
q = 2 * b ^ 3 / 27 / a ^ 3 - b * c / 3 / a ^ 2 + d / a
disc = q ^ 2 / 4 + p ^ 3 / 27
If disc >= 0 Then
    u(1) = -q / 2 + disc ^ (1 / 2)
    If u(1) = 0 Then u(1) = -q
Else
    u(1) = (q ^ 2 / 4 - disc) ^ (1 / 2)
    If q = 0 Then u(2) = [pi()] / 2 Else u(2) = Atn(((-disc) ^ (1 / 2) / (-q / 2))) + IIf(q > 0, [pi()], 0)
End If
 
If u(1) <> 0 Then
    For i = 1 To 3
        uc(1) = Abs(u(1)) ^ (1 / 3) * IIf(u(1) > 0, 1, -1) * Cos((u(2) + i * 2 * [pi()]) / 3)
        uc(2) = Abs(u(1)) ^ (1 / 3) * IIf(u(1) > 0, 1, -1) * Sin((u(2) + i * 2 * [pi()]) / 3)
 
        arrResult(i, 1) = uc(1) * (1 - p / 3 / (u(1) ^ 2) ^ (1 / 3)) - b / 3 / a
        arrResult(i, 2) = uc(2) * (1 + p / 3 / (u(1) ^ 2) ^ (1 / 3))
    Next i
End If
CubicEq = arrResult
End Function
 
Upvote 0
Hi again Monir

I analysed your post and the problem is in cell M31. The fact that you are rounding an intermediate result in your (the solver's) algorithm will not allow excel to go beyond a certain precision.

Try with M31 equal to simply =COMPLEX(K31,L31) and you'll see that the solver will meet the target.
 
Upvote 0
Hi Nate;

Thank you for the links you kindly provided. I've accessed the 3 links and reviewed their contents.

As you know, root finding is different than modeling of data, regression and data fitting.

I've just posted a reply to PGC that includes a 4th order equation with known roots and the results from my Solver attempt.

Any comments ??

Regards.
 
Upvote 0
Hi PGC;

1) Thank you for your courtesy in posting the code. I'm sure I'll find useful applications for your FUNCTION CubicEq().

2) As per your suggestion, I've adjusted the formula in cell M31 to read:
M31::=COMPLEX(K31,L31)
and got for the same cubic equation:
Root M31:: 327.992927010736+7.68204388990057E-011i
Target K34:: -0.0046373
Constraint L34::0.000052
The target is still not satisfied despite "Solver found a solution"!
Did you actually get a target value closer to zero ??

3) Here's another example:
f(y) = y^4 + s y^3 + t y^2 + u y + v = 0
where:
s = 13.22725669
t = -660.9686635
u = 2171.065948
v = 26894.83922

The above quartic equation has the following roots:
...y1 = 12.7610238150129+1.08692420304653i
...y2 = 12.7610238150129 -1.08692420304653i
...y3 = -4.83472945228703
...y4 = -33.91457487027

4) Enter:
B27:: poly coefficient "s"
B28:: poly coefficient "t"
B29:: poly coefficient "u"
B30:: poly coefficient "v"

K57:: enter initial estimate of root real coefficient "a"
L57:: enter initial estimate of root imaginary coefficient "b"
M57:: complex root "a+bi"
........=COMPLEX(K57,L57)
K58:: evaluate equation at root M57
........=IMSUM(IMPOWER(M57,4),IMPRODUCT(IMPOWER(M57,3),B27),IMPRODUCT(IMPOWER(M57,2),B28),IMPRODUCT(M57,B29),B30)
K60:: separate the real part
........=ROUND(IMREAL(K58),7)
L60:: separate the imaginary part
........=ROUND(IMAGINARY(K58),7)

5) Solver :
target: K60=0
by changing: K57, L57
subject to constraint: L60=0
Solver Options:
...precision = 1.E-7
...convergence = 1.E-7
...use automatic scaling
...estimates = Quadratic
...derivatives = Central
...search = Conjugate

6) Numerical example:
Enter the following values:
K57:: 10
L31:: 1

7) Run Solver:
"Solver found a solution. All constraints and optimality conditions are satisfied."

Root M57:: 12.7610238516371+1.08692409407958i
Target K60:: 0.0001887
Constraint L60::0.0000818
Do you consider the above resulting values of target and constraint to be close enough to zero ??

9) Keep in mind that we know for fact that the version of XL Solver is quite unreliable and the commercial version of Solver (by Frontline; I think) is not any better! One expects, however, that XL Solver would behave reasonably well for simple tasks as this.

Regards.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Using goal seek within VBA
Replies
0
Views
658
waptrick
W
A
  • Question
Create LAMBDA function to substitute the Goal Seek or Solver Functions
Replies
2
Views
1K
AEAA
A
G
  • Question
Feet Inches Sixteenths Number Format
Replies
5
Views
643
Phuynh
P
M
  • Question
Excel VBA goal seek loop
Replies
1
Views
827
BSALV
BSALV
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
478
kevin9999
K

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top