Maximum point of graph

[Matt Splat]

Hello All

Can some one please show me how to find the absolute maximum (x & y)
from a 4th order polynomial equation...

Excel tells me the equation is;
Y=-0.007x³+0.0321x²-0.4409x+3.6696

The equation was derived from the following x,y points;
(12.27,1.721),(14.43,1.762),(17.03,1.808),(19.22,1.790)

I know the absolute maximum is approx (17.6,1.810) by physically
graphing and then reading of the max point but is there any way Excel
can calculate this for me?

Any thoughts appreciated, thanx in advance

Mat

 

[Jerry W. Lewis]

Terminology: This is a 3rd order polynomial, not a 4th order.

Caveat: Unless you have external knowledge that the form of your model
(cubic in this case) is exactly correct, it is ill-advised to to
estimate as many parameters as you have data points. You will get a
perfect fit to the observed data, but interpolation/extrapolation may be
totally meaningless.

Equation: If you want to calculate with the coefficients returned by the
chart trendline, you either need to format that equation to scientific
notation with 14 decimal places, or David Braden's VBA code
http://groups.google.com/[email protected]
to extract coefficients from the chart into cells. Note that you can
calculate the coefficients using the LINEST worksheet function.

Maximum: Assuming that your x data is in A1:A4 and your y data is in
B1:B4, put 17.6 into A6, and
=TREND(B1:B4,A1:A4^{1,2,3},A6^{1,2,3})
into B6. Then use Solver to maximize B6 by changing A6.

Jerry

 

[Jon Peltier]

Jerry -

I wish I'd seen your post before writing the exact same solution in the
other thread.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jerry W. Lewis]

When I see a multipost that I have responded to elsewhere I usually post
a reference, but till you said something, I hadn't noticed. It probably
would have done no good, since Matt ignored the two answers in
microsoft.public.excel hours before he initiated this thread.

Jerry

 

[Jon Peltier]

You can lead a horse to water.... Actually, the horse stumbled his way
to the water.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______