Hi PGC;
Thank you once again for your tremendous help, time and patience.
1) At last, I've just been able to run the Solver procedure successfully and almost in full agreement with your latest results!
Here's what I did. I copied the Excel file from my main computer A (XL 2003 & Win XP Home) to a 2nd computer B (XL 2000 & Win XP Home) and to a 3rd computer C (XL 2003 & Win XP Prof). The Solver procedure worked fine and produced reasonable results on computers B and C. I saved the file and copied it back to computer A from computer C. Now it works fine and produces very reasonable results.
For the cubic equation real root:
..Root: 327.992927017581
..Target: -3.12924385070801E-007
I don't have a clue on what was the problem and I've no intention (at present) to investigate it!
2) At this point I believe that the Solver procedure has run its course and there would be no added value in pursuing it any further. You said it correctly: "you can get no better using solver"
I suppose we should move on to a general and more reliable procedure.
3) One of the apparent difficulties in using the Solver procedure is that its success, if at all, crucially depends on having a good first guess of the root, which by no means is readily available for a general equation. Equally problematic is the fact that the user of the Solver procedure has no control on hunting the root down even if it is accurately bracketed.
4) This brings me to your recent suggestion of writing a UDF to determine all the roots of a polynomial, possibly using the Newton-Raphson method. The method, if I recall correctly, requires the 1st derivative of the function to be provided, which is not a problem, but more importantly one must first bracket the roots either by a different procedure or by providing an array of guesses (as you suggested) and let the procedure zooms in each root to within a specified convergence criterion.
5) Such methods deal with real polynomial coefficients and determine real roots. Instead, I would suggest the Languerre's method. It is guaranteed to converge to all types of roots in the - oo to + oo range, handles polynomials with complex coefficients, and does not require an initial guess or starting point or trial solutions. (Keep in mind, with complex coefficients, complex roots may or may not occur in conjugate pairs.)
Sounds too good to be true ?? Well, it's actually true! I successfully used the procedure years ago and even recommended it to grad. students.
6) NR, p. 264, 265 has the Languerre's routine (~ 30 lines of code) and its deriver ZROOTS (~ 30 lines). Both codes are in F77 and are relatively easy to follow and convert to VBA. You would use your FUNCTION CubicEq() as a template to write the procedure. It is really that simple, specially for someone with your expertise in XL VBA.
7) The developed root-finding procedure would be applicable to any one-dimensional equation of the form:
f(x) = sum [k=1 to k=N+1] A(k) x^(k-1)
where f(x) has only one independent variable "x", N is the degree, and A(N+1) are the complex coefficients of the polynomial.
8) It cannot be overemphasized that one should always try to get some idea of how the function behaves before trying to find its roots. There's really no excuse for not to, since it is a one dimensional and the task can't be more simpler in XL.
The display would identify where the function changes sign and whether the change in sign is associated with a real root, a finite jump discontinuity, or a singular point. Also, potential problems such as double real roots (value of function is zero at its max or min), multiple real roots in close proximity (oscillating function about the x-axis), and other problems, could easily be identified from a simple display automatically generated on the w/s by the UDF (just a thought).
9) One could validate the developed UDF using the analytical data for 3rd and 4th degree equations. There're no general analytical solutions for polynomials of degree higher than 4, though there're (very complicated) solutions for particular families of polynomials of any degree. One can't, however, justify the effort required in pursuing such solutions. Rather, one may manufacture such solutions by multiplying a lower degree equation (with known roots) by a known real or imaginary root.
I'd like to know what you think about the whole thing.
(It would be a good idea to continue the discussion under a new thread "Roots of 1D Polynomials of any Degree with Real or Complex Coefficients", just in case someone might have already done that, and it is a matter of polishing the procedure. Would you like me to do that and copy the above as OP??)
Regards.