[Go back to CEM 333 Course Information Page][Go back to CEM 333 Home Page]

An Excel Spreadsheet Exercise*

Question Use the data in the table below to construct a spreadsheet showing the titration curve data, the first-derivative data and the second-derivative data. Plot each of these results versus titrant volume and determine the end point of the titration.

Volume AgNO3, mL

E vs. SCE, V

Answer We will first enter the data into the spreadsheet and construct an ordinary titration curve. Enter the data from the table above and use the Chart Wizard to plot the ordinary titration curve as shown in Figure 1. This plot is made as an XY(scatter) plot. Name the Workbook, POTTITR.xls and save it.

Figure 1.

Now we will compute the first derivative. In order to make room for these to line up with the appropriate volumes let us copy the data (columns A and B from Figure 1) into a separate sheet, Sheet2, leaving the original data and plot in Sheet1. Now, in Sheet 2, insert blank rows between each pair of data points as shown in Figure 2. Label columns C, D, E, and F with "[Delta]E, V", "[Delta]V, mL", "[Delta]E/[Delta]V, V/mL" and "[Delta]2E/[Delta]V2, V2/mL2" respectively. The first derivative between the first two points (5.00 and 15.00 mL) is the difference in cell potential [Delta]E = (0.085 - 0.062) V divided by the difference in volume [Delta]V = (15.00-5.00) ml. For cell C3, calculate [Delta]E as =B4-B2 and copy the result into the odd numbered cells in column C (C3 to C37). For cell D3, calculate [Delta]V as = A4-A2 and likewise copy the formula into the odd numbered cells in column D. In cell E3 the first derivative is calculated as =C3/D3. This result is placed in cell E3 because it is associated with a volume midway between the first two volume points.. This same formula can be copied into the other cells in column E.

Figure 2.

Now we will compute the second derivative and enter the results in column F. The second derivative can be estimated as the change in [Delta]E per unit change in volume. Hence, for the [Delta]E values in cells E5 and E3, the change in [Delta]E is (0.107-0.085) - (0.085-0.062) V. This is associated with a change in volume [Delta]V of (15.00-5.00) mL. We can compute this in Sheet 2 as =(E5-E3)/(A4-A2). This formula is placed in cell F4 because it is the second derivative associated with the average volume of 15.00 mL. The resulting calculations should be as shown in Figure 2.

Now we must prepare the first and second derivative plots. It is best to remove the blank rows before plotting so that Excel will have data in each row. Copy the data in columns A and E of Sheet 2 into a new Worksheet, Sheet 3. Paste column A, Sheet 2 into column A, Sheet 3. Paste column E, Sheet 2 into column C, Sheet 3. Be sure to use Paste Special and paste the values only. For the first derivative plot, the volumes associated with each value of the derivative can be calculated from the average of the two volumes used to compute the derivative. Thus for the first point of the derivative, the average volume is (15.00+5.00)/2 = 10.00 mL. For the second point, the average is (20.00+15.00)/2 = 17.50 mL and so on. Enter the formula = Average(A2,A4) in to cell B3 and copy this into the blank cells in column B. Name column B "Mean Volume". Now we will remove the cells without numbers in them. Arrange to plot the data using the Chart Wizard and the XY (scatter) plot. The resulting chart should appear as in Figure 3.

Figure 3.

The second derivative data will now be copied into a new Sheet 4. Copy (Paste Special) the data of columns A and F from Sheet 2 into Sheet 4. Now the blank rows can be removed directly since the second derivative data align with the volumes. Remove the blank rows and redundant columns so that Sheet 4 looks like Figure 4.

Figure 4.

Plot these data using the Chart Wizard to obtain the plot shown on the right side of the figure. If you would like to determine the end point very precisely, you can manipulate the axes to locate the equivalence point. Here the end point is seen to be approximately 24.33 mL. This could have also been done on the first derivative plot, but it is somewhat easier to find the zero crossing than it is to find the maximum.

*Courtesy Prof. S.R. Crouch.