On Wed, 25 Jan 2017 10:34:47 -0800 (PST), BottleMan wrote:

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?

First, using your data: Excel doesn't show me the formula you posted.

I see:
y = -0.0006x4 + 0.038x3 - 0.5525x2 + 2.5491x - 1.8375
R² = 0.9816


Then: Excel shows the constants at reduced precision.

Using the poorly documented, unintuitive, and cumbersome 'linest'
function, these are the coefficients to more decimal places for your
data:

-0.000567859
0.038003496
-0.552495008
2.549077276
-1.837537486


=LINEST(F5:F12,E5:E12^{1,2,3,4},TRUE,TRUE)

Your y-values are in column F, your x values are in column E.

You weren't expecting the x-values to appear first in the formula,
were you? Why should M$ bother with usual conventions?

The quite user-unfriendly procedu
1. Enter that formula above (with the cryptic call for a 4th order
polynomial)
2. Highlight the cells where you want the coefficients to appear
I highlighted cells in a row. As I recall, that is required. Highlight
enough cells to receive all your coefficients.
3. Then the best part (a brain fart from some coder after too much
coffee):
3.a: Press F2 to edit the cell where the formula is.
3.b: Press ctrl-shift-enter (after saying "Mother may I' forwards
and backwards three times)

Then ... it is an array. So if you want to change something, you
can't. You need to redo it in some cells that aren't an array already.