Maximum of a formula

[Guest]

I have a 6th order polynomial fit of data points. I want to find the number
that would give the maximum value of this curve. Is there a formula that
would do this without creating a large table and evaluating the table for the
Max?
(eg. derivative where slope = 0 indicates maximum)

 

[Guest]

Solver will do it. Is there any way to automate Solver so that it works like
a worksheet function?

 

[Dana DeLouis]

(eg. derivative where slope = 0 indicates maximum)

Hi. Just to mention. A derivative of zero on a 6th order polynomial means
it could be either a Maximum, or a Minimum. You would want to test the 5
solution points, and pick the largest. If you take the second derivative,
and substitute each of the 5 solutions, a positive number would indicate a
relative minimum, and a negative number would indicate a relative maximum.
Not sure where you want to go from here.

 

[Bernard Liengme]

How is your calculus?
Let do it with a cubic d*x3+c*x2+b*x+a
Differentiate to give 3d*x2+2c*x+b This will = 0 if the value is min or max
Now we could solve this quadratic but with a 6th order fit the differential
will be a 5th order equation for which there is no closed solution.

You cannot make Solver behave like a function but you can make a macro to
run Solver (look in MS Knowledge database, I think there is an example
there; or try www.solver.com)
best wishes

 

[Guest]

I am not aware that Excel can perform derivatives or integrals. Solver will
find the max for me but I need to run it "manually" to use the results in
further operations. ( I have about 19 of these curve fits in 1 file/7
worksheets). It would be nice to have an automatic way to do this.

 

[Dana DeLouis]

One thing that comes to mind about Solver is that in a 6th order polynomial,
Solver may zero in on the wrong local minimum/maximum. Do you have a lot
of data points? Maybe you could smooth your curve with a 3 degree
polynomial, or perhaps run a cubic spline equation.

 

[Guest]

Thanks for the warning on Solver. My data is fairly smooth with minor
ripples so I don't think it will pick a bad solution.
Where do I find cubic spline info?