computer series, 13 1
edited by JAMES P. BIRK Arizona State University Temps, AZ 85287
Advanced Titration Plots Using Spreadsheet Scripting James 0. Currie, Jr. and Richard V. Whiteley, Jr. Pacific University, Forest Grove, OR 971 16 The calculation of pH plots for acid base titrations can be an important partof the general chemistry and quantitative analysis curricula. However, even in quantitative analysis, the level of understanding achieved by the students is often disappointing. This is certainlv due to the approach taken to s i b g thkse problems: In oider to avoid generating a higher degree polynomial, "simplifying" assumptions are usually made, and the problem is reduced to, a t worst, a second-degree polynomial. However, it requires considerable insight to recognize when assumptions are appropriate and useful, and, without this insight, these assumptions could complicate the problem more than simplify it. While it might be ~edagoPicallvimportant to master the simplifying assumption, it is no longer necessary to make such assumutions to avoid havine to solve hieher deeree polynomials. Personal computers are readily available and Droerams for solving ~olvnomialsfor titration plots (1. 2 ) k c b u f f e r systems-(3) have been extensively 'developed. Consequently, students can use programs that employ a single polynomial expression, derived from mass balance and charge balance requirements, to generate an entire pH titration plot, with no simplifyingassumptions. And with a minor extension of the promam, one can calculate ionic activities rather thanconcenirations, thereby using the full power of the computer to generate truly accurate pH . plots. . These programming techniques have been extended to the spreadsheet ( 4 , 5 ) .The newer spreadsheets offer extensive graphing functions and have provided a useful approach to this problem. However, in their native form they suffer from several limitations. If a suitable cuwe reauires 250 points, the cell formula for the polynomial expr&ion must be reoeated 250 times. one for each point. An iterative solution would require another 250-cell formula for each iteration. Fillinerhe cells is not hard hut theresultinesheet has thousands i f cells filled with complex formulas straining memory and disk space. The approach is also not very flexible in that it does not easily adjust to different calculation options or number of points. Wingz, produced by Informix Software for the Macintosh computer, is the first of the spreadsheets to go beyond simple macro capabilities by providing its own development lanmage called Hvperscript. Not only can this language auiom&e normalsireadsheet functions, but it also allows them to be used with all of the logic and loop structures characteristic of any high-level programming language. Operations can occur with traditional variables and constants or with values obtained from designated cells. Described herein is a n entirely self-contained Wingz application for describing ionic con&nrrations and activitlcs throughout the tltratlon ofany weak arid or weak base with a strong base or strong acid, respectively Dissociation
-
.
u
-
"
A
-
-
-
constants for numerous weak acids and bases are stored in a srrollable list box along w~rhappropriate ion parameters needed for calculating actlvlts mcfficlents The user I R given several graphingoptions tor describing the acid1base titration. We see the actual use of this program in general chemistry simply as a tool to illustrate changes in a pH plot when various parameters are changed. This could be done either as a classroom demonstration or as an assimment. Conversly, a student in quantitative analysis couid be expected to produce a simple version (i.e., without controls) on their own. For this &onp, underst&nding the wrrection for activities can have great value. The scripting approach nicely combines programmingand spreadsheet techniques, even if the plots are not automated. We as faculty use the program to produce plots for presentations, student exercises and exams. Calculations By combining mass balance and charge balance requirements with a u ~ r . o ~ r i aionic t e eauilibrium exuressions. it is possible to write explicit, exact polynomial equations in terms of anv one ion of interest. and even for sixth or seventh degree polynomials, the c~mputer can quickly find the one. real root to anv prescribed level of accuracv. The general' acid-base titratibn cuwe program developed by Breneman (1) was used to generate Hi (or OH-) ionic concentrations. From these values, appropriate dissociation constants, and volume corrections, the wncentration of all relevant ions is calculated and stored in the spreadsheet and can be plotted as a function of pH or volume of titrant added. From the titration data, the spreadsheet also expresses each ionic wncentration as a fraction of the total concentration of sister species as a function of pH. For example, in phosphoric acid, the fraction of the phosphoric acid existing as HzPOQas a function of pH can be displayed as which is .A
Conseauentlv. ". the titration can be described verv thoroughly in terms of ionic concentrations and ionic distributions. However, the computer's problem-solvinc~Dower is largely wasted when it i s b e d to solve polynomi~~forionic concentrations alone because it is the ionic activitv that is the issue in an acid base titration, and activit; is only crudely approximated by concentration. Hence, the "ionic activity" option is made available to the user. It is entirely understandable why concentrations are used in lieu of activities for most equllibnum calculations. Calculationsfor ionactl\~ltyoftcncrcateaparadox: Inorder Volume 68 Number 11 November 1991
923
more elaborate calculations for y are necessary Such calculations are not implemented in this program. With an a ~ ~ r o x i m art for e each ion. all dissociation constants can be corrected from their zero'ionic strengthvalues used a t the onset of the calculation. A true dissociation constant might be expressed as
where d is the activity. Clearly, when the ionic strength is taken as zero, all ys become essentially 1.00 and we have the equation whereinionic strength is ignored. The correction to K, is effected by dividing it by the approximate y's, giving
Figure 1. Calculation loop to know the true equilibrium constant, one must know the ionic strength of the solution, but in order to know the ionic strength of the solution, one must know the concentration of each ion; solving for the concentration of each ion, unfortunately, requires a knowledge of the true equilibrium constant! This must be solved by a n iterative Drocess. For the computer, this is a trivial extension of the ikicconcentration problem. It is important to note that using activity coefficients directly to calculate activities, without correcting the dissociation constants and recalculating ion wncentrations, can lead to larger errors than if activities had been ignored altogether (Fig. 1). A scrollable list box includes the dissociation constants for numerous weak acids and bases at zero ionic strength. Hence, a first calculation is made assuming zero ionic strength, and from this, an approximate IH+l (or [OH-]) is calculated. This is used to calculate the concentration of every relevant ion in solution at that particular point in the titration. With approximations for-the concehtration of every ion, the ionic strength, w, of the solution can be
ci is the concentration of ion i and tiis its charge. Then from the extended Debye-Huckel equation, logy =
hZ (1+ B a G )
the activity coefficient, y, for each ion is calculated. A and B are constants that are determined by the temperature and dielectric constant of the solvent. For water a t 25 OC, these are 0.5092 and 0.328, respectively. The variable a is a n adjustable parameter, measured in Angstrom units. The a values for 130 ions have been determined by J. Kielland (6); they are stored along with the dissociation constants in the scrollable list box from which they are automatically called when the "ionic activity" option is chosen by the user. When a is not known, we supply a default value of 4.0. For all neutral species z is obviously zero which, according to Debye-Huckel theory, puts the activity coefficient of any neutral molecule a t 1.00. It is auite correct (within a few oercent) to take Y as 1.00 wheniolution concentrations areiess than about 0:5 M. For our DurDoses. we have rewrded the activitv coefficient for all deu'a~ mhecules as LOOfor all concentrations and all values of F. It should also be noted that when p exceeds about 0.5 M, the extended Debye-Huckel law becomes inaccurate, particularly for multiply charged ions, and so, 924
Journal of Chemical Education
Now, the entire calculation is repeated with every appropriate dissociation constant corrected. Abetter approximation for IH' . 1.(or . IOH-I) . .. is generated. and from this better approximations for every relevant ionic concentration are made. and therefore. a better aonroximation of 9s is made. Surprisingly, these second approximations are quite good, and it has been determined that a third a~nroximationis superfluous. With accurate activity coeffilients the ion activities are calculated one last time. The resulting H+ activity leads to an exceptionally accurate pH plot. Moreover, it is Dossible to demonstrate the effects of ionic strength m a particular acld base titration. The user can select a fixed ionic strenarh o~tion.eauivalent to an ionic strength buffering situation, or the ;sir can select an initial ionic strenah, equivalent to performinp a titration in the presence of: strong electroly&. The effcts on the shape of the titration curve are generally subtle, but the exercise is instructive, nonetheless.
-
a.
Scripting In Wingz every action that can be done manually can be done in a script. For example in a typical spreadsheet, the formula for calculating the average of five points might be "=AVG(AI..A5Y where A1 and A5 are the cells containing the data to be averaged. If this formula was placed in cell A6 the average of cells A1 through A5 would be evaluated each time recalculations were done. Alternatively a script could be written that would place this formula in the A6 cell: Put "=AVG(Al..AS)"into A6
After the script is executed the formula "=AVWAl..A5Y will occupy the cell "A6" and be evaluated each time recalculations are done. If 100 averages needed to be calculated for columns A through CC, a script could place the wrresponding formula into the appropriate cell and then calculate the result. Thus a newworksheet couldstart out empty but have a complete set offormulas after executing a simple script. Used this way a script is simply a macro language that automates an activity you could do manuallv. Wingz Hyperscript proh;les an even better alternative. We wuld define variables for the oortions tn be averaged and do the entire calculation fromihe script. define 3mw,col,result,vdues,range~to~avg,end~range row=l values=5 end-ranged00 for col=1to end-range range~to~avg=makerange(col,row,col,row+v~s~ result= avg(range(range-to-avg)) put result into makeeell(col,row+vdues+l) end for
Figure 2. Wingz screen with control Structures There are obvious economies to using an eight-line compiled script to substitute for 100 cells of formulas. The less obvious advantage is that the limits of the calculation are defmed by variables that can be changed from within the script. Amore elegant variation is to name cells in the sheet with the names row, col, result, values, rangeto-avg, and end-range. The contents of these cells define the limits of the calculation and they are easily modifiable by the user. The Hyperscript language contains all the normal programming logic and looping commands that can be applied to any location on the sheet. Thisincludes all the predefined statistical and database functions that go far beyond normal programminglanguages. All the formatting and charting functions can also be implemented from scripts. Topdown programming organization is fully supported in that functions are easily created and called from the main program. Recursion (a function that calls itsem is supported. The completed script can be run manually from a pdldown menu but this is not typical. Instead scripts are normally attached to the sheet and set to operate each time the ~~~-sheet is activated or when the mouse button is pressed. They can also be attached to control structures heated on the scrrenand run each time thestructure isactivatcd. The most versatile approach is to attach all the functions to the sheet and cull them when thevariouscnntrolstructures are activated, ~~~~~~
Figure 3. Phosphoric acid line plot of pH vs. volume of titrant.
~
User Interface One of the special things about developing an application in the Wingz environment is the easy incorporation of a user friendly approach to input and output expected by Macintosh users. ORenfmstratingis the uncertainty of the allowable range for numeric or text values. Wingz solves this problem by permitting many different control structures such as "control buttons", "list boxes", "check boxes", and "number wheels". Figure 2 shows the screen presented to the user of this application. The user does type in the reagent concentrations, increments of addition, and appropriate volumes but nearly everything else is changed by using the control structures. For example, a long list of acids, their dissociation constants, and ion activity parameters are contained in the spreadsheet. When an acid is selected using the scrollable list box, its name and the corresponding constants are automatically entered in the places provided. These places are named cells whose contents are directly used in the calculations. Users can modify any number or text by simply typing their own choice. Clicking on the "radio button" for bases brings up a similar list of bases. Radio
Figure 4. Phosphoric acid plot of ion distribution vs. pH.
Figure 5. Phosphoric acid plot of ion distribution vs. volume of titrant. Volume 68 Number 11 November 1991
925
buttons also ~ r o v i d ethe means for selection of the eraph type and a size appropriate for the computer in use. Multiple titrations may be calculated with each new one having a change in the reagent concentrations or the dissociation constants. Number wheels are used to select reasonable values for the display of these plots. The calculation and plotting process and replots after changing graph types are initiated using large push buttons. The progress of the calculations is displayed prominently. The normal spreadsheet column and row headings, entry bar, and selection tools can be displayed using a smaller push button.
Figure 6. Diprotic acid plot with increment factor of 0.1 in dissociation constant.
Plots Manually generating plots from a table of data is somewhat more tedious with Wingz than with graphing-only software packages such as Cricket Graph of DeltaGraph. However, with scripting every operation is automatically performed after completion of the calculations. The traditional plots of volume of titrant vs pH (Fig. 3 ) are only one of the many plot options. These plots can use data generated with or without consideration of activities or even for titrations done a t constant ionic strength. Data on ion distribution is maintained in each case so plots of ion distribution versus pH and ion distribution versus volume o f t ~ t r n nareootiom t Fies. 4 and 5 I.All the plotsareshown as they were generated. Enhancements for publication could have been done by simply cutting and pasting them into another graphics program, but this would not represent the program's true capabilities. Starting values can be repeatedly changed and a series oftitration plots generated and plotted simultaneously. For example the shape of the curve near the endpoint might be analyzed by looking a t a series of plots with decreasing dissociation constants (Fig. 6). Or the effect of dilution on the clarity of the second endpoint in the titration can be easily observed (Fig. 7). Wingz can generate three dimension plots. The multiple line plots mentioned previously can be plotted a s a wireframe or a continuous surface plot (Fig. 8).To match the different monitors, the gradations can be distinguished using different patterns, shades of gray, or the colors we associate with pH changes. The color plots are quite stunning with the smooth gradation fromred a t pH 1.0 through green a t 7.0 to blue a t 13.0. (A color version of Figure 8 is shown on thecover ofthis issue.) The number of gradations and the pH of the center point in the gradations can be varied. ~~~~~~
Figure 7. Diprotic acid plot with increment factor of 0.1 in concentrations.
~
~
Conclusion Wingz represents a new development environment that is easy to learn and use. I t has the sophistication and versatility that go beyond most previous environments. Its usellness as a tool to generate pH plots either in a classroom setting or for research has been described. We think that our approach demonstrates a more complete solution to the description of pH titration plot than has been typical in the past. We expect chemists to find i t an exciting alternative to solving other chemical calculations. Copies of this spreadsheet are available from the authors by sending $10 to cover the cost of the disk and postage. Wingz is a product of Informix Software and is required for this application. Literature Cited 1. Brenrrnuk, G. L. J Chem. Edve 1914.52.872.
Figure 8. Surface plot with increment factor of 0.1 in dissociation constant.
926
Journal of Chemical Education
2. 3. 4. 5. 6.
Willis, C. J. J Chem Educ 1981.58.659. Lamben, W. J. J . Cham. Educ. 1990.67,150. Parker. 0. J. J. Chem. Educ 1990,67,A5.
Tucker.E.E.;Thompson.L.C.:Poe,D.P.AmLab Kielland. J. J. Am. Cham. Soc. 1931,59,1675
