Absolute Maximum

  • Thread starter Thread starter Matt Splat
  • Start date Start date
M

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
 
Matt -

First of all, make sure you have enough digits in your coefficients. If
that is from the trendline formula, it's tremendously accurate, but
imprecise. Double click on the data label that holds the formula, and
change the number format to a scientific format with lots of digits
(like 14).

y = -7.39375705995915E-04x3 + 3.20620647935437E-02x2 -
4.40893080397058E-01x + 3.66955823485892E+00

Second of all, check the formula, because it's 0.0007 x^3, and you had
0.007.

Instead of copying the values from the trendline formula, you could use
LINEST, as described by Bernard Liengme in his website
(http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm). This is
actually what I did.

Then what I would do is set up two cells, one with a value of X, and
another that uses the coefficients above to calculate Y from this X. Use
Solver (an addin that ships with Excel but might need to be activated
from Add-ins on the Tools menu) to set the value of the Y cell to a
maximum by changing the value of the X cell. The winning entry:

(17.64302772,1.810487518)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
See the responses to the other post on the same subject in the same
newsgroup.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top