Message Boards Message Boards

[?] Solving polynomial equation derived from trendline format In Excel?

Posted 6 years ago

I have created a chart using Microsoft Excel. I added a polynomial trend line to the created chart, and derived a nonlinear equation for polynomial trend line as y = 0.0008x6 + 0.0013x5 - 0.1054x4 + 0.6517x3 - 1.493x2 + 0.9054x + 0.8343 with R² = 0.9998. I solved the equation by excel by substituting all values of X (From 0.5, 0.75,1, 1.25, 1.5 to 4) into an equation to have the result of Y for each value of X.

The problem is that for the last four values of X from 3.25, 3.5, 3.75 & 4 (in the last part of table) are given wrong result of Y or even in minus values.

I have attached the Excel sheet for more details and help please.

Attachments:

Seems that Excels's graphics is somewhat misleading.

With

y[x_] := 0.0008 x^6 + 0.0013 x^5 - 0.1054 x^4 + 0.6517 x^3 -1.493 x^2 + 0.9054 x + 0.8343

Try

Plot[y[x], {x, 3, 4}]

and you see that short above 3.4 negative values occur.

Perhaps you should try another model? (data are the x, y pairs in your Excel-sheet)

nlm = NonlinearModelFit[data, a Exp[-b (x - c)^2], {a, b, c}, x] // Normal

This gives

y = 101.708 E^(-0.326251 (-0.102446 + x)^2)

and this will never have negative values.

enter image description here

Attachments:
POSTED BY: Hans Dolhaine
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract