I don't think Excel gave me the right trendline equation.

**MJA** · 08-12-2012, 04:11 PM

I was plotting the data below for a report:

Angle Current

46, 3.1700E-02
48, 2.5225E-02
50, 1.7300E-02
52, 1.1750E-02
54, 4.7000E-03
56, 3.4500E-03
58, 4.1500E-03
60, 7.9000E-03
62, 9.1500E-03
64, 3.6750E-02
66, 6.3625E-02

The data is non-linear and I needed to estimate the minimum of the function for the calculations I am doing. The plan was that I use a high-order polynomial approximation and take it's equation. I would take the first derivative of the equation and solve for all the minimum.

But here's the problem. I plotted the data and took a 5th order polynomial approximation. The equation that Excel gave me was
y = 9E-08x⁵ - 2E-05x⁴ + 0.0024x³ - 0.127x² + 3.3615x - 35.455

It seemed a little fishy so I decided to take it to WolframAlpha: WolframAlpha Plot

The graph of the trendline and the graph on WolframAlpha look nothing alike (the y-values on each are orders of magnitude different). Did Excel give me the wrong equation?

Please refer to the attached workbook. I am referring to the graph on the second sheet. (The sheet is called 'Part B') The columns of data are highlighted in yellow.

Thanks for any help!

**shg** · 08-12-2012, 05:07 PM

You're not using sufficient precision in the coefficients.

Please Login or Register  to view this content.

The array formula* in J2:O2 is

=LINEST(F2:F12, A2:A12^{1,2,3,4,5})

The reconstruction formula in H2 and copied down is

=SERIESSUM(A2, 5, -1, $J$2:$N$2) + $O$2

That looks like a pretty good fit to col F.

* Select all cells, paste the formula in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.