Polinomial graph

[Hugh]

I have the following equation on my graph's trendline.
y=0.0148xsquared +0.0375x-0.0398.
If x=2 would the formula for trend be
((0.0148*2)^2)+(0.0375*2)-0.0398?
Thanks

 

[Tushar Mehta]

Close, but not quite. The formula is y=ax^2+bx+c not (ax)^2+(bx)+c.
So, you want (0.0148*2^2)+(0.0375*2)-0.0398. Actually, the brackets
are also not necessary because XL's operator precedence ranks
exponentiation above multiplication above addition. Hence,
0.0148*2^2+0.0375*2-0.0398 is the same as the above equation.

And, of course, if the value of x were in some cell, say, A4, then one
would enter, in some other cell, the formula
=0.0148*A4^2+0.0375*A4-0.0398

One final note. The trendline equation is not very accurate unless you
increase the number of digits shown. Double-click the equation in the
chart. Alternatively, use the LINEST function. Contrary to MS's
documentation, it works for polynomials. See Bernard Liengme's
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

Far more than you ever wanted to know about polynomial equations and
XL, eh? {grin}

--
Regards,

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

 

[Guest]

Hugh,

That looks correct. Another useful check would be to substitute 5 or 6
different points for X. Then chart the line. It should look exactly like
your built-in trendline.

For example, you are already substituting 2 for X below. In cell A1 add the
number 1, in cell A2 add 2, in cell A3 add 3, in cell A4 add 4, an so on.
Then in cell B1 reference your equation. Copy the equation down the range of
column B to match the points in column A. Then chart column A as the X axis
and column B as the Y axis. The charted trendline should look just like your
built-in trendline.

A B
1 =(0.0148(A1)^2)+0.0375(A1)-0.0398
2 =(0.0148(A2)^2)+0.0375(A2)-0.0398
3 =(0.0148(A3)^2)+0.0375(A3)-0.0398
4 =(0.0148(A4)^2)+0.0375(A4)-0.0398
5 =(0.0148(A5)^2)+0.0375(A5)-0.0398
6 =(0.0148(A6)^2)+0.0375(A6)-0.0398

 

[Hugh]

Many thanks John,
I don't seem to get the same curve when I use the actual x, as opposed to
the 1,2,3 test figures, hence the belief that I must have had the formula
wrong.
I'll try it again and see if I can get a fit.
Are there any rules that that could be throwing my graph off, like 0s, or
too high x numbers?
Hugh

 

[Michael R Middleton]

Hugh -

Also, use an XY (Scatter) chart type, not a Line chart type.

- Mike

www.mikemiddleton.com

 

[Jon Peltier]

And follow Tushar's advice to display many more digits of the trendline
equation.

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