trend line equations dont match data

It is difficult without details (and example spreadsheet would go a long way towards helping with this).

Since we are using the term "trendline", I'm going to assume you are using the chart trendline feature for these regressions, and not the LINEST() function. What chart type are you using -- line or scatter or other? This can be important, because a line chart may very well use different x values in the regression than you think it is using.

Ultimately, these kind of questions tend to be about knowing exactly what is going into the regression. Can you give us an indication of exactly what is going into the regression?

Originally Posted by RobH1961

thanks for telling me how to load sheet

See Sheet2 in the attached file.

Your first mistake: you used a Line chart type. That uses {1;2;3;4;5;6} for the x-axis in calculating the trendline instead of the actual data in F5:F10, which is used only for labels for the x-axis.

Instead, use an XY Scatter chart of some form. I prefer markers only for the data when I am plotting a trendline.

Second, the trendline label should be formatted to display 15 significant digits in Scientific format, as I described previously.

We could copy-and-paste each 15-digit coefficient into J1:M1.

Instead, I demonstrate how to use LINEST for the order-3 polynomial that you chose. Select J1:M1 and array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=LINEST(G5:G10,F5:F10^{1,2,3})

Excel displays the formula surrounded by curly braces. We cannot type the curly braces; that is just how Excel indicates that the formula was array-entered.

Format J1:M1 as Scientific with 14 decimal places. Note that the coefficients are similar to, but exactly the same as the coefficients in the trendline label. In this case, they are close enough: there are same to 11 or 12 decimal places.

To compare the actual y-data (G5:G10) with the estimated y-data (H5:H10), enter the following formula into H5 and copy down through H10:

=SERIESSUM(F5,3,-1,$J$1:$M$1)

To compare, calculate R^2 with the following formula in J10, formatted as Scientific with 14 decimal places:

=RSQ(H5:H10,G5:G10)

Again, note that it is similar to R^2 in the trendline label. In this case, they are the same to 13 decimal places.

So in this case, you can use LINEST to generate the coefficients.

But when you are unsure of how to use LINEST for some trendline, or if LINEST fails (as it does, sometimes), you can manually copy-and-paste the trendline label coefficients if they are formatted as Scientific with 14 decimal places.

Also note that SERIESSUM is useful only for polynomial trendlines. For other trendlines, be sure to use the proper formula in the Excel worksheet.