Information • Textbooks • Media • ResourcesJChemEd.chem.wisc.edu • Vol. 75 No. 1 January 1998 • Journal of Chemical Education 119A powerful tool that is widely available in spreadsheetsprovides a simple means of fitting experimental data to non-linear functions. The procedure is so easy to use and itsmode of operation is so obvious that it is an excellent wayfor students to learn the underlying principle of least-squares curve fitting. The purpose of this article is to intro-duce the method of Walsh and Diamond (1) to readers ofthis Journal, to extend their treatment to weighted leastsquares, and to add a simple method for estimating uncer-tainties in the least-square parameters. Other recipes forcurve fitting have been presented in numerous previouspapers (2–16).Consider the problem of fitting the experimental gaschromatography data (17) in Figure 1 with the van Deemterequation:y = Ax + B/x + C (1)where y is plate height (mm), x is flow rate (mL/min), andA, B, and C are constants to be found by the method of leastsquares. This paper is restricted to the situation in which theuncertainty in y is much greater than the uncertainty in x.We treat cases in which (i) all values of y have equaluncertainty or (ii) different values of y have different un-certainty. In case i, each datum is given equal weight forcurve fitting. This procedure is the default (unweighted)method used when uncertainties in y are not known. Caseii is a weighted least squares treatment, because more cer-tain points are given more weight than less certain points.Unweighted Least SquaresExperimental values of x and y from Figure 1 are listedin the first two columns of the spreadsheet in Figure 2. Thevertical deviation of the ith point from the smooth curve isvertical deviation = yi (observed) – yi (calculated)= yi – (Axi + B/xi + C)(2)The least squares criterion is to find values of A, B, andC in eq 1 that minimize the sum of the squares of the verti-cal deviations of the points from the curve: n sum= yi– Axi+B /xi+C2Σi =1(3)where n is the total number of points (= 13 in Fig. 1).Here are the steps to find the best values of A, B, andC that minimize the sum in eq 3:1. List the measured values of x and y in columns 1 and2 of Figure 2.2. Temporarily assign the value 1 to A, B, and C at theright side of the spreadsheet in cells F2, F3, and F4.(The labels in column E are for readability. They haveno other function.)3. In column C, calculate y from the measured value of x(eq 1). For example, in cell C2, y is computed from thevalue of x in cell A2 and the values of A, B, and C incells F2, F3, and F4.Nonlinear Least-Squares Curve Fittingwith Microsoft Excel SolverDaniel C. HarrisChemistry & Materials Branch, Research & Technology Division, Naval Air Warfare Center,China Lake, CA 93555Computer Bulletin Boardedited bySteven D. GammonUniversity of IdahoMoscow, ID 83844Figure 1. Plate height versus flow rate in a gas chromatographyexperiment. Circles are experimental data (17) and the solid lineis the best fit to eq 1 by the method of least squares in Figures 2–4.Figure 2. Initial spreadsheet for finding the best values of A, B,and C in eq 1. Numbers in columns A and B are experimentaldata. Numbers in column F are initial guesses for A, B, and C.The sum in cell D16 is the one to be minimized in eq 3.Information • Textbooks • Media • Resources120 Journal of Chemical Education • Vol. 75 No. 1 January 1998 • JChemEd.chem.wisc.edu4. In column D, compute the vertical deviation in eq 2and then square the deviation. For example, D2 = (B2– C2)^2.5. In cell D16, compute the sum of the squares of verticaldeviations in column D. The sum in cell D16 is thesum in eq 3.6. The least squares criterion is to find values of A, B, andC that minimize the sum in cell D16. Microsoft Excelprovides a tool called Solver that handles this prob-lem in a manner that is transparent to the user. Solveris invoked in different manners by different versionsof the software. In version 5, Solver is found under theTools menu. After invoking Solver, the screen in Fig-ure 3 appears. If cell D16 was highlighted prior to call-ing Solver, then “$D$16” automatically appears in theupper left dialog box that says “Set Cell”. If some othercell was highlighted, enter D16 in the Set Cell box. Thedollar signs are optional. Because we wish to minimizethe value in cell D16, click “Min” on the second linebeside “Equal to”. Finally, write “F2,F3,F4” in the dia-log box labeled “By Changing Cells”. Now click the“Solve” button at the upper right and you have justasked the software to set the value of cell D16 to aminimum by changing values in cells F2, F3, and F4.7. When Solver finishes its task in a few seconds, thespreadsheet will appear as in Figure 4. Solver has ad-justed the values in cells F2, F3, and F4 to minimizethe sum in cell D16. The values of A, B, and C in cellsF2, F3, and F4 were used to plot the curve in Figure 1.18. Try some different initial values for A, B, and C (otherthan 1) to see if Solver finds the same solution. Agiven problem may have many local minima. We areseeking the best set of A, B, and C to find the lowestminimum sum in cell D16.Weighted Least SquaresIf different values of y have different uncertainties, itmakes sense to force the least-squares curve to be closer tothe more certain points than to the less certain points. Thatis, we assign a greater weight to the more certain points. Ifthe uncertainty (standard deviation) in the measured valueof yi is si, then the weight assigned to point i isweight = wi = 1/si2(4)In Figure 5, measured uncertainties in y are listed incolumn C under the heading “error(y)”. Weights computedwith eq 4 appear in column D. Columns E and F are calcu-lated with eqs 1 and 2, just as they were in Figure 2. Col-umn G contains weighted square residuals, obtained bymultiplying the square residuals in column F times theweights in column D. Cell G16 contains the sum of weightedresiduals. Solver is then invoked to vary the values of A, B,and C (in cells G20, G21, and G22) to minimize the sum ofweighted residuals in cell G16. The final values of A, B, andFigure 4. Appearance of spreadsheet from Figure 2 after Solverhas finished its operation.Figure 3. Solver screen with user input.C are somewhat different from the final values in theunweighted procedure in Figure 4.Estimating Uncertainties in the Least-SquaresParametersUncertainties in A,