Excel polynomial fitting

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... 

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. 

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. 

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-... 

By most statistical measures, this was an excellent fit of the data (for example r2 = 0.9929). However, this correlation was deemed unsatisfactory for two reasons. First, the equation had a curvature that was not indicated by the data set. This is a common problem when one attempts to fit a set of data with a polynomial. Second, it resulted in significant errors in the low-density region. Seeming small statistical errors translate into large errors in the estimate viscosity. 

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... 

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. 

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. 

In this example, the polynomial approximation to the form of the radial wave function gives an excellent fit for small values of x i.e. close to the nucleus), but it fails to reproduce even one radial node [a value of x for which R x) = 0]. 

McCabe-Thiele calculations are easiest to do on spreadsheets if the y versus x VLE data are expressed in an equation. The form y = f(x) is most convenient for flash distillation and for distillation columns (see Chapter 4) if stepping off stages from the bottom of the column up. The form x = g(y) is most convenient for distillation columns if stepping off stages from the top down. Built-in functions in Excel will determine polynomials that fit the data, aldiough the fit will usually not be perfect. This will be illustrated for fitting the ethanol-water equilibrium data in Table 2-1 in the form y = f (x ). (Note An additional data point Xg = 0.5079, = 0.6564 was added to the numbers in the table.) Enter the data in the... 

To find a polynomial that fits the data, first highlight the x-y data. Then go to the Excel tool bar and click on Layout Analysis Trendline More Trendline Options. Choose polynomial as type and in the menu select the desired order of the polynomial. (You can try different orders to find which has the best fit.) Make sure the boxes Display Equation on Chart and Set intercept = 0 are checked. Then click Close. 

Figure 4.6 shows that regression of Ami At versus At to a second-order polynomial gives an excellent fit with = 0.997. 

The response of the ECD is not linear over a large concentration range, and the curvature varies with the compound. Therefore, calibration curves have to be determined, one for each halocarbon. again each one with at least seven points. The calibration values can be fitted to polynomial equations e.g., Microsoft Excel Solver). The curve fitting should be done stepwise, first assuming a second order equation and then refining to higher orders if needed, until the deviations of standard points from the curve are within acceptable limits. 

A polynomial fnnction is the first approach suggested by most of the curve-fitting tools available (e.g.. Excel, graphing calculators, MATLAB). Usually, it is a univariate (single-variable) polynomial function with constant parameters given by... 

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... 

Fit a quench curve by intrapolation, preferably in Excel (e g. 6-degree polynomial) Calculate the difference between the theoretical and the fitted efficiencies. 

---