Using Excel to Fit a Titration Curve

An Excel spreadsheet has been developed to help you fit a theoretical titration curve to the pH vs. volume data that you collection in your pH titration experiment. The spreadsheet will enable you to determine the end point(s) of the titration as well as the p*K _{a}*(s) of your unknown acid. This document is quite long, so you may use the hyperlinks below to navigate if necessary.

- Getting Started
- Entering Your Data
- Changing the Range of Plotted Points
- Estimating the Equivalence Point Volume
- Transferring Your Data to Another Worksheet
- Entering Other Important Data
- Estimating
*K*s for Your Acid_{a} - Using the Solver

The first thing that you should do is to download the Excel spreadsheet file called acid_base_curve_fit.xls by clicking on the link. Once you have downloaded the file, run Excel, open the downloaded file, and click on the tab at the bottom of the screen that says **Differentiate**. The screen should appear something like the following figure.

Notice that the volume and pH data for a titration are shown in columns A and B, respectively. Data entry columns in this spreadsheet are highlighted in green. Just click on cell A2, and begin entering your data beginning with the first point. After entering each volume, hit the right arrow key so that for the first point, for example, the active cell will be B2, and you can then type the corresponding pH value. Then move the cursor to cell A3, click once, and enter the second volume reading followed by the right arrow key. Then enter the pH corresponding to the second volume. Move the cursor to cell A4, and continue until you have entered all of your pH vs. volume data in columns A and B.

It is that you enter the **total volume of base added** corresponding to each pH reading, not the buret reading.

As you will note, Excel calculates the first and second derivatives of the curve and places them in columns E and G. The worksheet also calculates the average value of the volume of base for each pair of points in your data set. These values will serve as the *x* axis for the derivative plots, examples of which are shown below. Remember that you will probably have more or fewer data points than are shown in the sample data set, so you will have to copy the formulas in columns C through F into cells to the right of all of your data points. For example, if your data look like this,

then click on the fill handle in the lower right hand corner of the highlighted box, and drag straight down to copy the formulas in C18 to G18 into rows 19 to 31 as shown below.

Notice that in row 31 the values are somewhat bizarre. This result occurs because in taking the differences, we obtain one fewer difference than we had original data points. So, we could have stopped dragging at row 30. You will also have to change the range of points to be plotted in the graphs as we shall see.

Now move to the part of the containing the titration curve, which should be similar to the one shown below.

Now right click in the white area in the border of the graph, and the window on the left below should appear as shown.

Click on __S__ource Data... to produce the window on the right above. Notice that the window shows how the plot will appear when you click OK. In the __X__ Values: box, we see =Differentiate!$A$2:$A$30. This entry indicates that the *x* values will be taken from cells A2 through A30 on the Differentiate worksheet. You may either edit the line directly by clicking in the box, moving the cursor with the arrow keys, and deleting or adding as necessary to indicate to Excel which cells should be plotted, or by clicking on the small red arrow at the right of the __X__ Values: box and using the mouse to select the proper cells. Do the same thing with the __Y__ Values: box, and then click on OK to produce the desired plot of the titration data. At this point, you may print a copy of your plotted data by clicking on the plot, and then clicking __F__ile/__P__rint on the menu bar at the top of the screen.

Now scroll to the area of the worksheet containing the derivative plots as shown below.

At this point, you may have to change the data selected for plotting as we did for the titration curve. The plot options are set to automatically scale the data so that the plot fills the entire space, but the options may be easily changed to zoom in on any part of the plot as we shall see. If you are fortunate, you will have very well-defined end points appearing as peaks in the first derivative plot and as the intersection of the curve with the *x* axis in the second derivative plot.

To find the equivalence point volume, we seek the point on the volume axis that corresponds to the maximum slope in the curve; that is, the first derivative should exhibit a maximum in the first derivative. Now move your cursor to point directly at one of your data points on the first derivative plot. A small box will appear as shown below.

Notice that the *x* (volume) and *y* (pH) values for the selected point appear in the box. If one of your points happens to coincide with the apex of one or more of your peaks, you can get an estimate of your end point volumes by simply placing the cursor on the point. Make a note of the approximate equivalence point volume(s) before proceeding.

Now focus on the second equivalence point in the second derivative curve. Calculus tells us that if the first derivative of a function goes through a maximum, the second derivative passes through zero at the same point on the *x* axis. Thus, we must find the zero crossing point on the *x* axis. We will expand the horizontal scale of the plot so that we may get a better estimate of the equivalence point, which is at the zero crossing point. It appears that the volume is about 50 mL at the second equivalence point, so we shall expand the scale so that the entire *x* axis covers only 4 mL and is centered on 50 mL. Your value will be different, but the principle will be identical. Now right click on the *x *axis, and the small window shown below will appear. To do this, you must find some area on the axis where there are no data points or lines other than the axis.

Now click on F__o__rmat Axis..., and the following window appears.

Click on the Mi__n__imum: box, and type a number that is two less than your estimate of the point where the curve crosses the axis, which in this example is 25 mL. So, we type 23, and then click in the Ma__x__imum: box, and type 27. You should type numbers that are appropriate for your data, and then click on OK.

A graph similar to the one above then appears, and it should be relatively easy to estimate the equivalence point volume, which in the example is about 24.8 mL. Once again, if one of your data happens to be exactly on the zero crossing point as one is in the plot above, you can place the cursor on the point as shown, and the coordinates of the point will appear in a box. Otherwise, you can estimate the point by eye. Jot down your best estimate of the equivalence point volume for use in the Diprotic Acid worksheet.

Now highlight your pH data in the Differentiate worksheet as illustrated below, and click on the Copy icon, or alternatively, you may use the menu bar, and click on **Edit/Copy **to place your pH data on the clipboard. Note that we have not copied cell B31 because this point is far beyond the second equivalence point. As a rule of thumb, it is unnecessary to use data that are more than about 30% beyond the last equivalence point. In fact, it may be advantageous to use only those data up to a point just past the last end point, particularly if your standard base contained an appreciable amount of carbon dioxide. In our example, we estimate the second equivalence point at about 50 mL, so the point at 115 mL (cell A31) is more than 100% beyond the second equivalence point. So, copy the data up to 70.6 mL added.

Click on the Diprotic Acid tab at the bottom of the screen, or the tab corresponding to whichever type of acid you appear to have. A worksheet should appear that looks similar to the one below.

Now, click on cell A15, and then click on ** Edit/Paste Special...** on the menu bar to reveal the window below.

Click on ** Values** and then on OK, and your pH data should be copied to column A beginning with cell A15. You may perform the same task using keystrokes by typing

If there are more data in your data set than are included in the sample file, you must copy the contents of columns B through L to include your data. Be sure to highlight the last **two **rows of columns B through L before clicking on the fill handle and copying the columns down through the last row of your data. This will ensure that there is a 1 in each cell of column L. If you highlight just the last row before copying, column L will increment as you use the fill handle to copy. It is imperative that column L contain only the number 1 in each cell.

Of course, if you have fewer data in your set than are included in the sample set, you must delete the rows in the sample set beyond your data.

You must now enter several other data into your worksheet in preparation for fitting the titration curve. Notice that cells into which you must enter data are highlighted in green, and cells containing values calculated by Excel are highlighted in blue. First, enter the volume of the solution *V _{a}* (Va in the worksheet), your estimate

where *n _{a}/n_{b}* is the number of moles of acid per mole of base. For the example of a diprotic acid, this ratio is 1/2. The value of

In cell B6 (Cb), you enter the concentration of the titrant NaOH that you standardized previously. In cell B7 (Va) enter the total volume *V _{a}* of the acid solution that you titrated. So, if you followed the directions in the experiment, you pipetted 50 mL of your prepared acid solution, and then you pipetted an additional 50 mL of water into the titration vessel. Thus

Enter a value for *V _{eq} *in cell B8 (Veq); Excel will calculate and display the equivalence point volume

Most nonlinear curve fitting procedures require preliminary estimates of the parameters that will eventually result from the fitting process, and Excel's solver is no exception. These estimates are entered in cells B1 and B2 in our example of a diprotic acid. The *K _{a}*s for a diprotic acid can usually be estimated quite easily from the titration curve. Now click on the Differentiate tab, and view the raw titration curve for your experiment. The curve for the sample data set is shown below.

In the sample data set, p*K _{a}*1 = 4 and p

If your equivalence points are well defined, you can estimate p*K _{a}*1 and p

Notice that only order-of-magnitude estimates of the *K _{a}*s are required to get Excel's solver started in the curve fitting process. So, in this example, the two half-titration points occur at 1/4 and 3/4 of the estimated second equivalence point volume of 52 mL. These points at 13 mL and 39 mL correspond to p

Now that you have entered all of the experimental data and initial estimates of the parameters in the spreadsheet, we may proceed with the curve fitting process using the solver.

Excel's solver function is a very powerful tool for solving equations and for curve fitting. The solver uses one of several numerical methods that are similar to the method of successive approximations that we have discussed in class. The solver allows a number of options to be selected by the user, and the choice of options very much depends on the job at hand. You may wish to explore some of the solver options after you have become familiar with its operation, but to begin, we shall provide fairly specific directions that seem to work reasonably well with titration data. This type of data is fairly unusual relative to most of the tasks for which Excel is normally used in that the parameters in acid/base titrations vary over many orders of magnitude. For example, in the titration illustrated above, the pH varies from about 2 to about 12, which represents a change in the hydronium ion concentration, and thus the hydroxide ion concentration, of 10^{10}. Such computations provide a major test of the robustness and power of the Excel solver computational engine, and in general, it does a magnificent job. It is important to recognize that Excel will provide answers that are no better than the data that you submit to it, however.

To begin, recall that in a least-squares procedure, the goal is to find a theoretical function that fits the experimental data and that minimizes the sum of the squares of the residuals; that is, we wish to minimize the sum of the squares of the differences between the experimental data and the theoretical curve. In our worksheet, the sum of the squares of the residuals is located in cell B11, which is highlighted in red. When it is instructed properly, the solver tries to systematically change the values of the parameters that you select in order to find a solution that gives the smallest possible number in cell B11. It is very important that you check the range of the summation in cell B11. For example, click on the cell, and you should find something like =SUM(H15:H56) in the formula bar. As a rule of thumb, you should include data in cell B11 from the beginning of the titration up to one or two points past the equivalence point. If your data set up to this point extends through row 75, for example, you must change the formula in B11 to read =SUM(H15:H75).

After you have checked and edited cell B11, you should make sure that all of your points are included in the plot of the data and the fitted curve. There are a couple of ways to do this. The first method begins by just clicking on one of the curves. Some of the points will be highlighted, and rectangular boxes will appear surrounding the *x* and *y* columns of data that are being plotted. You may then click on the fill handle in the lower right hand corner of each box and drag the rectangle to include all of your data. You should repeat this process of extending the range of plotted points for the fitted curve, which is plotted in red, and for the residual plot. Note that you are plotting all of the experimental and calculated points even though you are using only the points up to just past the equivalence point for the target cell B11.

Now, you may call up the solver by clicking on ** Tools/Solver...** in the menu bar, which causes the following window to appear.

Note that the Target Cell has been set to B11. For our purposes, the Target Cell is the cell containing the sum of the squares of the residuals as we noted above. If you wish to change the Target Cell, you simply click on the red arrow to the right of the S__e__t Target Cell: box, and select another cell. This change should be unnecessary for our task. Be sure that the Mi__n__ button has been checked to tell the solver that we seek a minimum in cell B11. For other tasks, it might be advantageous to find the maximum in a numerical function or find a particular target value. Since we are performing *least* *squares*, we want a minimum. You will also notice that the contains references to cells B1 and B2, which contain your initial estimates of the dissociation constants *K _{a}*1 and

The Max __T__ime: box is set for 60 seconds, which limits the amount of time that the solver spends trying to carry out the least-squares minimization. This value is much longer than is generally required. The __I__terations: box is set to 100. This value limits the number of times that the solver will compute the sum of the squares of the residuals, and once again our job seldom requires this number unless something is dreadfully wrong. The __P__recision:, Tol__e__rance:, and Con__v__ergence: boxes determine how close the results of successive iterations must be before the solver declares an end to the process and presents the results for evaluation. These options are set to extremely small numbers. The other options and check boxes are set to ensure that the solver finds only positive solutions and that the numerical methods that it uses are appropriate for our task. If you would like to learn more about the details of the options, click on the ** Help** button, and read them now.

When you are finished examining the options, click on **OK** to return to Solver Parameters window to begin the solution process. Finally, click on the ** Solve** button to start the solver. After a few to several seconds, the following window should appear, and a new set of numbers will be produced in cells B1 and B2.

At this point, you may click on **OK** to keep the solution. In other words, Excel will replace your initial estimates with the new values for *K _{a}*1 and

There are several ways to assess the quality of the fit of the dissociation constants. The most obvious indicator that the fit is acceptable is the graph of the experimental data and the fitted data. A typical example is shown below.

The experimental curve is shown in black, and the fitted curve is drawn in red. The plot has been expanded by clicking on the plot and dragging the handle at the lower right hand corner. You can do the same thing with your plot to examine it more carefully. To return the plot to its original small size, just click on the undo icon in the toolbar. Excel has done a reasonably good job of fitting the data in this example, but as you can see, there are deviations at high and low pH values. The fit may be improved in various ways, but a fit similar to the one shown should suffice to give you estimates of the dissociation constants that will enable you to identify the acid.

You may wish to allow the solver to refine your value of *V _{eq}* . To do this, return to the solver window, and change the

After you have carried out the fitting procedure, you should be sure that the results make good chemical sense. If you examine the typical values for diprotic acids in the table of dissociation constants under the **Ka's & pKa's** tab or in Appendix 2 in the back of your textbook, you will notice that with few exceptions, the values range from about 10^{-2} to about 10^{-11}. Values outside this range should offend your chemical intuition, and you should look for errors in your worksheet or in the solver options. When you are satisfied that you have fit the data as well as you can with the Excel worksheet, you should print a copy for your records, and use the results to calculate the molar mass of the acid, which along with the dissociation constants should enable you to identify the unknown acid.

At this point in your session, you should take some time to play with the variables in the worksheet. For example, change the value of the equivalence point volume *V _{eq}*, and note the effect on the curves. Change other variables such as the dissociation constants and see what happens. Never underestimate the value of intellectual play. There are other refinements that you can make in the analysis using corrections for activity effects. If you are interested in these refinements, please contact me, and I will guide you through the process. Eventually, I will add instructions to this document for using this feature.

This exercise and spreadsheet acid_base_curve_fit.xls is based on the article *Titration vs. Tradition* by Robert De Levie, *The Chemical Educator,* 1(3), **1996,** http://www3.springer-ny.com/chedr/.

*Version 1.3* 4/27/98 10:42 AM