Polynomial Regression Using Excel

Steps 1 and 2 Follow the instructions given above to plot a trendline. 

---

For statistical samples of small volume, an increase in the order of the polynomial regression of variables can produce a serious increase in the residual variance. We can reduce the number of the coefficients from the model but then we must introduce a transcendental regression relationship for the variables of the process. From the general theory of statistical process modelling (relations (5.1)-(5.9)) we can claim that the use of these types of relationships between dependent and independent process variables is possible. However, when using these relationships between the variables of the process, it is important to obtain an excellent ensemble of statistical data (i.e. with small residual and relative variances). 

Two approaches for interpolation function have been used. In one, polynomials, e.g., in powers of w", are fit to impedance data. Usually, a piecewise regression is required. While piece-wise polynomials are excellent for smoothing, the best example being splines, they are not very reliable for extrapolation and result in a relatively large number of peirameters. A second approach is to use interpolation... 

To use nonlinear regression, you minimize Eq. (E.3) with respect to the unknown parameters. Polynomial and multiple regression do this too (behind the scenes), but for nonlinear curve fits it is necessary to use functions such as Solver in Excel and fminsearch in MATLAB. This is demonstrated using the same example given above for multiple regression. 

The following method uses the method of least squares. In this case, all data points are used to generate a second-order polynomial equation. This equation is then differentiated and evaluated at the point where the value of the derivative is required. For example, Microsoft Excel can be employed to generate the regression equation. Once all the coefficients are known, the equation has only to be analytically differentiated ... 

Fit these data to an appropriate polynomial form. First, plot the data and successively fit a linear, quadratic, cubic, and quartic trendline, noting the value each time when the no longer improves, use the Excel Regression Add-On to find the parameters and statistical indicators. Verify that the polynomial chosen does indeed satisfy the usual statistical criteria. In all cases, the constant term must be zero since the (0, 0) data point is without error. 

---