Polynomial fitting using Excel

Choose the Type tab and then choose the appropriate fitting function from the gallery of functional forms. (Depending on the data in the series, the exponential, power or logarithmic choices may not be available.) If you choose the polynomial form you can select the order of the polynomial by using the spinner. If you choose 3, for example. Excel will fit a polynomial of order three... 

The difference between values of adjacent points is assumed to be linear function of the distance separating them. The closer a point is to an observation, the closer its value is to that of the observation. Despite the simplicity of the calculation, linear interpolation should be used with care as the abrupt changes in slope that may occur at recorded values are unlikely to reflect accurately the more smooth transitions likely to be observed in practice. A better, and graphically more acceptable, result is achieved by fitting a smooth curve to the data. Suitable polynomials offer an excellent choice. 

HI. [VBA required] Using the spreadsheet in Appendix B of Chapter 4 or your own spreadsheet, solve the following problem. A methanol water mixture is being distilled in a distillation column with a total condenser and a partial reboiler. The pressure is 1.0 atm, and the reflux is returned as a saturated liquid. The feed rate is 250 kmol/h and is a saturated vapor. The feed is 40 mol% methanol. We desire a bottoms product that is 1.1 mol% methanol and a distillate product that is 99.3 mol% methanol. L/D = 4.5. Find the optimum feed stage, the total number of stages, D and B. Assume CMO is valid. Equilibrium data are available in Table 2-7. Use Excel to fit this data with a 6th-order polynomial. After solving the problem, do What if simulations to see what happens if the products are made purer and if L/D is decreased. 

Rational function models inherit the advantages of the polynomial family, despite a less simple form, and can take on an extremely wide range of shapes. They have better interpolation properties (typically smoother and less oscillatory) than polynomial models, and excellent extrapolation powers due to their asymptotic properties. Moreover, they can be used to model a complicated structure to a fairly low degree in both the numerator and denominator. On the other hand, because the properties of the rational function family are often not well understood, one might wonder which numerator and denominator degrees should be chosen. Unconstrained rational function fitting may also lead to undesired vertical asymptotes due to roots in the denominator polynomial. 

From a least-squares fit on a graph using Excel, the polynomial is... 

The parameters are easily determined by using computer software. In Microsoft Excel, the data are put into columns A and B and the graph is created as for a linear curve fit. This time, though, when adding the trendline, choose the polynomial icon and use 2 (which gives powers up to and including x ). The result is... 

Whenever possible, line and bar graphs should be constructed with the use of computer graphing programs (CricketGraph, Excel, Lotus, etc.). Aside from the fact that they produce graphs that are uniform and visually attractive, they have the added capability of fitting non-ideal data to a best-fit line or polynomial equation. This operation of fitting a set of data to a best-fit line becomes extremely im-... 

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. 

Few texts show how the polynomials are obtained but today it is easy to use a program such as Microsoft Office Excel to fit a trend line polynomial (Figure 4.8) to the modem data given in the CRC Handbook [8]. Options within the trend line permit scientific notation extended to four significant figures (tap on the polynomial and then right click) to obtain results shown here. 

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. 

---