Curve Fitting with Excel

Curve jStting deals with finding an equation that best fits a set of data. There are a number of techniques that you can use to determine these functions. You will learn about them in your numerical methods and other future engjneerii classes. The purpose of this section is to demonstrate how to use Excel to find an equation that best fits a set of data which you have plotted. We will demonstrate the curve-fitting capabilities of Excel using the following example. 

To add the trendline or the best fit, with the mouse pointer over a data point, dick the 

Tke lY (Scatter) plot of aperinental data points without the data points connected. 

In order to examine how gpod the linear equation P = 0.5542x fits the data, we compare the force results obtained fix m the equation to the actual data points as shown in Table 14.9. As you can see the equation fits the data reasonably well. 

Find the equation that best fits the following set of data points in Table 14.10. 

---

You should know how to perform curve fitting with Excel. 

Mortimer, R. G., 1999. Mathematics for Physical Chemistry, 2nd ed. Academic Press, San Diego, CA. [This book contains an introduction to computer use with brief comments, references and sources to BASIC, Excel, graphics, curve fitting, and Mathematica.]... 

The pH dependence of could be due to changes in A-B loop disorder rates, perhaps the chemical exchange phenomenon observed for NPl-ImH (Section ll,E,2,b), or to changes in ligand bond strength. The change in lies in the off-rates (Tables I-Ill) consistent with the loop disorder model. Plots of vs pH display an excellent fit with the equation for a titration curve (Fig. 21), indicating that the transition... 

The best-fitting set of parameters can be found by minimization of the objective function (Section 13.2.8.2). This can be performed only by iterative procedures. For this purpose several minimization algorithms can be applied, for example, Simplex, Gauss-Newton, and the Marquardt methods. It is not the aim of this chapter to deal with non-linear curve-fitting extensively. For further reference, excellent papers and books are available [18]. 

To dearly distinguish between these two modes of solvent penetration of the gel, we immersed poly(acrylamide-co-sodium methacrylate) gels swollen with water and equilibrated with either pH 4.0 HQ or pH 9.2 NaOH solution into limited volumes of solutions of 10 wt % deuterium oxide (DzO) in water at the same pHs. By measuring the decline in density of the solution with time using a densitometer, we extracted the diffusion coefficient of D20 into the gel using a least squares curve fit of the exact solution for this diffusion problem to the data [121,149]. The curve fit in each case was excellent, and the diffusion coefficients obtained were 2.3 x 10 5cm2/s into the ionized pH 9.2 gel and 2.4 x 10 5 cm2/s into the nonionized pH 4.0 gel. These compare favorably with the self diffusion coefficient of D20, which is 2.6 x 10 5 cm2/s, since the presence of the polymer can be expected to reduce the diffusion coefficient about 10% in these cases [150], In short, these experiments show that individual solvent molecules can rapidly redistribute between the solution and the gel by a Fickian diffusion process with diffusion coefficients slightly less than in the free solution. 

Curve-fitting computation technique was applied for the calculation of formation constants of calcium lactate in methanol-water, ethanol-water, and glucose-water systems (60) with excellent results. In the calculation of the average formation constant Kav( = (Ki K2)i), P turned out to be about 190 for the methanol-water system, 192 for the ethanol-water system, and 185 for the glucose-water system. The value of b was 204. 

Spreadsheet Applications. Applications of Microsoft Excel in Analytical Chemistry, by Stanley R. Crouch and F. James Holler, treats in detail the spreadsheet approaches summarized in the text. This supplement contains 16 chapters that lead the student from basic concepts and operations to using spreadsheets for simulations, curve fitting, data smoothing, curve resolution, and many other topics. Topics in this companion book are correlated with topics in the text. See pages xvii and xviii for a correlation chart. Summaries in the text point to specific chapters and sections in the companion book. For added value and convenience, this ancillary can be packaged with the text. Contact your Thomson Brooks/Cole representative for details. 

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. 

Wagenet et al. (1977) found excellent agreement between observed urea, NH4, and NO3 effluent concentrations and theoretical curves determined from Eq. [lla,b,c] (Fig. 10-8). Adsorption of urea and NH4 by the soil was assumed to be instantaneous, with parameters and k2 estimated independently of the transport model. Urea hydrolysis, nitrification, and denitrification were assumed to be governed by first-order kinetics with i determined independently of the transport model and 2 and 3 estimated by curve fitting Eq. [llb,c] to observed NO3 and NH4 effluent concentrations. The magnitude of the rate coefficients was in the order i > 2 3, with single model-fitted values of 012 and 3 (i.e., 2 = 0-01 0 3 = 0.001 h ) sufficing for all soil columns. 

---