Excel trend line equation miscalculation problem

You have made one of the common mistakes that many make when dealing with high order polynomial trendlines in the charts -- you have assumed that the output is "exact". If you expand the number format (Select trendline label -> format trendline label -> number -> select an appropriate number format [I prefer a scientific format with at least 6 digits]), you will see that, for example, the first coefficient is not exactly 2E-20, but it is really 2.10959...E-20.

I prefer to perform regressions in the spreadsheet using the LINEST() function. The helpfile shows how to do this for polynomials: https://support.microsoft.com/en-us/...rs=en-us&ad=us

In this case, I notice that your data are almost a straight line on a log-log plot. I might try a different regression equation to see if I could reduce the number of parameters needed to fit the data.

The problem is that your trend line is showing only 1 significant figure for each coefficient. That's going to give you huge errors on a 6th degree polynomial. You'd do better to use LINEST Function. To obtain the 6 coefficients and the intercept, I used this formula in cell A64 copied down To A70

Formula:

Please Login or Register  to view this content.

Then, just to be sure, I calculated y values in Column I and % Diff from theoretical in Col J

If you have 30 graphs, 30 data sets, and 30 polynomials (or other linear equation) to process, then LINEST() [or ChemistB's INDEX(LINEST(...))] is the way to go. Once you get the LINEST() function set up once, it will usually be a relatively simple copy/paste to perform the same regression on other data sets. With the chart trendline, you will make 30 copies of the same chart, then manually copy 30 sets of equation parameters into the spreadsheet for use. I find it much simpler to use LINEST() in the spreadsheet when there are multiple data sets to process.

Okay. Just last few things to clarify. Does this soluton work for polynomial of degree 6? in other words, does this solution work for any graph of any shape? Secondly, can this technique work for extrapolation of points?

LINEST() can perform any "linear" regression (polynomials are all linear functions as are other function types like exponential and power functions). Once you have the coefficients of the polynomial, you can use those coefficients to interpolate or extrapolate or whatever else you might want to do with them.

As Mr Shorty stated, it will be much easier to have Excel calculate your 7 times 30 coefficients each time and then refer to those cells rather than manually copy and pasting from 30 different trendlines. I suggest you review the equations in my spreadsheet or the examples on the page Mr Shorty linked to get a better idea of what the equations are doing.

For comparison, I added the trendline from the chart and got the same coefficients, so this seems to be the correct 6th order polynomial from a linear least squares fit of the data. Other than being a relatively poor fit of the data, in what way is it not calculating as expected?

When I chart your raw data and your polynomial data, I see that the "best fit" curve is shaped like "waves" going above and below the data. This can sometimes be a consequence of using high order polynomials as best fit equations. Are you required to use a high order polynomial for this analysis? Unless you are aware of a better 6th order polynomial fit, I would be inclined to say that this is the best linear least squares fit to a 6th order polynomial. If you want a better fit, you need a better fitting equation (I suggested something in log-log space earlier) or a better fitting algorithm (something other than linear least squares).

There's a lot that can be said about curve fitting as part of data analysis -- most of it is not specific to Excel. The first (non-Excel) step is choosing your fitting equation. What kind of data is this? What do you know about the expected behavior of the phenomenon you are analyzing that can help choose a fitting equation.

As I noted, based only on the data, it looks fairly well behaved in log-log space (select each axis in your chart -> format axis -> check the "logarithmic axis" box). On this basis alone, I might suggest a fitting equation like log(y)=A*log(x)^2+B*log(x)+C and get A, B, and C in Excel from =LINEST(LOG(known_y),LOG(known_x)^{1,2}). This seems to give a much better fit and uses only 3 adjustable parameters.

I can't right now, but it should be as simple as replacing the LINEST() function inside of the existing functions with the one I gave above (with appropriate references to known_y and known_x). What difficulty are you having editing the existing INDEX(LINEST(...)...) formula? Don't forget to change the formula in column I to reflect the new regression equation.

However once, I have the formula then I can manipulate it.

I was hoping this would be the case, as I am not yet able to edit your sheet for you. I'm not sure what manipulation/edit you are having trouble with, so let's walk through the edits for ChemistB's formula in A64 (parts to change highlighted in red)

=INDEX(LINEST($H$63:$H$311,$G$63:$G$311^{1,2,3,4,5,6}),1,ROWS($A$62:$A62))

1) We will be using a 2nd order polynomial instead of a 6th order polynomial, so delete the ,3,4,5,6 from the exponentiation array =INDEX(LINEST($H$63:$H$311,$G$63:$G$311^{1,2}),1,ROWS($A$62:$A62))
2) We need the logarithm of the known_x argument, so nest the $G$63:$G$311 reference inside of a LOG() function =INDEX(LINEST($H$63:$H$311,LOG($G$63:$G$311)^{1,2}),1,ROWS($A$62:$A62))
3) We also need the logarithm of the known_y argument, so nest the $H$63:$H$311 inside of a LOG() function as well =INDEX(LINEST(LOG($H$63:$H$311),LOG($G$63:$G$311)^{1,2}),1,ROWS($A$62:$A62)). Our new formula is

Please Login or Register  to view this content.

4) copy into A65 and A66 to get all three parameters. Delete A67 to A70

5) Now we need a new formula in column I that is equivalent to our regression formula log(y)=A*log(x)^2+B*log(x)+C. From what (I assume) you know about logarithms, this is equivalent to y=10^(A*log(x)^2+B*log(x)+C), so let's enter this formula into I62 =10^($A$64*LOG(G62)^2+$A$65*LOG(G62)+$A$66). Copy/paste/fill into I63 to I311.

I assume that those are all edits you are capable of. Which edit do you have trouble with?

As I explained in the other thread, there is no single universal answer to curve fitting problems. The basic idea will work for any "linear" function where you want to use a least squares algorithm, but that is only one major subset of all possible regression algorithms. It is certainly one useful tool in your toolkit, but it cannot be expected to work in every possible scenario.