On Monday, January 23, 2017 at 2:53:04 AM UTC-5, Wally W. wrote:
On Fri, 20 Jan 2017 10:22:01 -0800 (PST), BottleMan wrote:

Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.

What do you mean by, "when I use this formula?"

Are you using the full precision of the constants produced by Excel?

I have found with nonlinear regression that rounding the constants can
produce results surprisingly far from the expected values.

I use the exact formula that Excel displays. Here is a typical data set:
x-Axis Y-axis
1.2 0.5
1.6 1
2.4 1.5
3.6 2
4.8 1.5
6 1
7.2 0.6
8 0.3
Here is the formula Excel displays:
y = 0.0046x4 - 0.0455x3 - 0.0981x2 + 1.5795x - 1.1698 with R² = 0.9843
Here are the values I get when I use the formula on the same x-values to calculate the Y-values:
x-Axis Y-axis
1.2 0.54
1.6 1.07
2.4 2.20
3.6 3.56
4.8 5.08
6 7.31
7.2 6.98
8 -18.11
That is even though the curve that Excel draws hugs the values very closely.. So what is going on?