EV 311: Water
Spreadsheet Calculations: Acid-Base Equilibria of Carbonic Acid

In this problem, you will be modeling the acid/base chemistry of carbonic acid. You will need to set up a spreadsheet in Excel. Across the first row, label the columns as [H+], [OH-], denom, pH, [H2A], [HA], and [A], pH, log [H+], log [OH-], log[H2A], log[HA], and log [A], (pH is done twice for ease of graphing). Under the pH column add numbers from 0 to 14 in increments of 0.25. You can do this easily by typing in the values 0 and 0.25 to cells D5 and D6. Highlight these two cells and drag to box pH 14. The numbers will all fill in sequence. In boxes A1-A2, write the names Ka1 and Ka2. In cells C1-C2 write the names pK1 and pK2. Add the pKa values for the carbonate equilibria to cells D1 and D2. In cells B1 and B2, place the formulas to determine Ka from pKa. Highlight box B1, go to the Insert window and highlight Name and then Define. Excel assigns the name Ka1 automatically, so press OK. Do the same for Ka2 and Ca (analytical concentration). This operation defines these values as constants rather than variables (you can also use the label of $B$1 in your formulae to show that the value in box B1 is a constant rather than a variable, but as you will see, the define name command is easier here). In the appropriate (A) cell, write a formula to determine the concentration of the H+ based on the pH. After writing the formula, drag it down to fill in the entire column. Next do the same for OH- and fill in the B cells. In the appropriate box, write a formula that represents the denominator for the alpha fractions. Fill in the appropriate boxes with the formula. Now write formulae for the [H2A], [HA], and [A] based on the [H+], the appropriate equilibrium constants (that you defined earlier) and the denominator that you calculated (in column C). Next, fill in the rows for the log[H2A], log[HA], and log [A], (use the alpha values already calculated). When you are finished drag down to all the appropriate rows. Highlight all the cells that should have a scientific number format, pull down the Format command and highlight Cells and then Number. Click on Scientific and then on OK. Next click on the cell in the extreme upper left (above the number 1 and next to A). This cell highlights the entire spreadsheet. Pull down the Format command and highlight Column and Auto Fit Selection. Plotting: Highlight the boxes pH, log [H+], log[OH-], log[H2A], log[HA], and log [A], (including the labels). Choose the Chart button and highlight a section just below the pKa values for your graph. Follow the directions (X-Y Scatter, X-Y Scatter, 1, etc.). You now have a plot of log[X] as a function of pH. To make it look better, double click on any axis that you want to change. A dialog box will come up. Click on Scale to change the scale and position of the axes (make sure you undo the x-marked auto boxes). The suggested scale is log[X] from 0 to -14 and pH from 0 to 14. Remove the grey background by clicking on the grey color and highlighting automatic and then choosing a white color. When you are satisfied with the graph, then you can print a copy. To print a copy, double click on the graph, choose the File command, then Print Preview. Check your graph and then instruct the program to Print. Questions: 1) On your log [X] vs. pH graph, identify the predominant species of carbonic acid in each pH range by writing its chemical structure. Make sure you correctly write the charges on each species, where appropriate. 2) Calculate the concentration of each of the three carbonate species at pH 7.0. Explain your calculation briefly. 3) Circle and identify the five intersections corresponding to titration points and the initial point on your graph. For each intersection: a) Which two chemical species are equal in concentration? b) Label the intersection as the initial point, midpoint 1 or 2 and equivalence point 1 or 2 assuming you are titrating with an acid as you did in teh alkalinity titration. c) Which labels would change if you wer etitrating with a base? d) Solve for pH at each intersection by looing at the graph. 4) Plot the concentration vs. pH instead of the log[X] vs. pH.  Discuss briefly when this might be more useful.