computer series, 167 Spreadsheets in General Chemistry: Their Introduction and Application to Complex Titrations John P. Chesick HaverfordCollege Haverford, PA 19041 Modern spreadsheet packages provide practical solutions for the general problem of making available the power of computers for data processing by large numbers of students in physical science courses. Instruction in the programming languages, such as FORTRAN, BASIC, and Pascal, is no longer necessary for the bulk of data processing involved in chemistry at the undergraduate level. The current generation of spreadsheets permit relatively complex calculations that meet the needs of most chemistry course applications and many smaller research problems. However, the time demands for the introduction of any new method like the spreadsheet must always compete with the primary subject matter of the chemistry course. This instruction time must come from time ~reviouslvdevoted to chemical principles and laboratok practice. It cannot simolv be another "add-on" in the oroeram. Time in any course program must be made for instruction in the use of the soreadsheet. and the methodolow -.for doing this continues to be a topic for discussion. Afew years ago our department first worked to incorporate spreadsheet use into the integrated junior laboratory course for all chemistw maiors. After this ex~eriencewe included it for all stuientsin the laboratoryf associated with the sophomore physical chemistry course. These students were mostly prospective chemistry or cell biology maiors. Most recentlv we have included spreadsheet use as an integral part of the laboratory associated with the second semester of our full-year general chemistry course. This course is not specifically designed for or solely populated by science majors but is intended for our general student population. Edwards, McKay, and Sink (1)have described a somewhat similar program incorporating spreadsheet use in the freshmaniaboratory of their course for science majors. We felt that our approach and goals differed enough from theirs to warrantthis report. We particularlv wanted to introduce spreadsheets a t a point in the laboratory program where thky would be necessarv or clearly useful as a n alternative to the usual "hand" calculatibns. It seems contrived to introduce the spreadsheet before it is clearly needed for the laboratory work. We find beginning students need a lot of help in organizing the work to be done with any mode of data processing; our experience is that a phased approach to independent work is essential. This requires a carefully structured Droaam in the initial work with s~readsheets. The mechakcsof working with the spreadshe& tool for the first time should not be mixed with lessons that oresent diff~cultiesin deciphering the chemistry.
."
&
-
Software and Hardware The college provides clusters of Macintosh computers for student use. They are connected to a sewer system that 934
Journal of Chemical Education
JAMESP. BIRK Arizona State University Tempe, AZ 85287-1604
eives the students access to MicrosoR Word. the current iersion of the Microsoft Excel spreadsheet pickage, printers, and other capabilities. All our spreadsheet work in these courses is done with this Excel package. As many as 25 students can be accommodated at one time in one room with individual computers. They can all access the Excel package a t once, permitting group tutorial instruction in the use of the system. Students are required to provide only their own disks for storage of their programs. Aside from the specific requirement for attendance a t the first group tutorial session, students are free to work a t any time the computer cluster centers are open. Applications By the start of the second semester, many of the fresbmen students have had some experience with word processing using microcomputers, but very few have had any exoeriencewith soreadsheets. Laboratow work in the first sehester generaliy does not require calEu~ationsof sufficiently repetitive or complicated nature to justify the introduction of the spreadsheet. However, the laboratory work of the second semester includes experiments with higher data processing demands, so the beginning of this term is the natural time for introducing the soreadsheet. We allocated two weeks of the laboratory time at the start of the second semester for the sole purpose of training and practice in the operations associated with beginning spreadsheet use. The Tutorial The first laboratory period was devoted to a 90-min tutorial for each group of 25 students, each with a computer. During this time the group was lead through the exercise of preparing the spreadsheet for generation of a strongacidlstrong-base titration curve. The calculations were carried out assuming complete reaction of the reagents, and the pH was computed from the concentration of the excess reagent. The student received a one-page copy of Figure 1showing the headings and data to be entered in columns A through H of the individual spreadsheet. The structure of the spreadsheet is thus clearly defined. This page accompanies an additional 2% pages of text that describe the calculations and summarize the steps for entering the headings and the data items as shown in Figure 1, the s~ecifiedformulas in cells E2 and B11 through H11. desired number formats, and the final plot to bemade.'column I of Fieure 1shows the student the values that should finally appear in column H if all the specified operations have been correctly done. Computer center staff lead the student group through all of the o~erationsdescribed in this text and also the ooeration of ihe Excel Chart Wizard plotting utility. he mi'st m deoartment staff are also ~ r e s e n during t this tutorial exercise to help individual stuhents. This 90-min tutorial was sufficient for almost all of the students to finish this exercise and to produce the required printouts. The balance of the 3-h lab period was released time, which could be used by students who needed more private time and possibly more help for the completion of
1
I
1
I
I[H]
1
I [OH]I1 nblna
I
1P1
IpH
IAnswer
I
I
I
I
I
1
mL Ivtot. L inb, mol ((na-nb)Ntot
I
.oo
Analyzing a Data Set for a Titration The 3 h of the second week of laboratory time was also released for a similar passlfail exercise involving a much more complicated titration problem: the pH titration of a solid sample of an unknown weak dibasic acid by a standard NaOH solution. The students are again provided with similarly detailed directions for the preparation of the prescribed spreadsheet, but there is no group tutorial. The students use the same spreadsheet operations that were presented in the first week.
Sample Data Set Figure 2 shows the headings, spreadsheet organization, and a sample data set given the students for use in this exercise. This data set includes the weight of the solid acid sample, the standard base molarity Mbrthe volume of water Voinitially used to dissolve the solid acid sample, and the sample set of base volumelpH titration data. These values corresponds to the experimental data that would be recorded in the pH titration of an unknown dibasic acid. The information provided in this spreadsheet exercise additionally identifies the endpoint volume Vend and provides values for the dibasic Figure 1. Strong base-strong acid titration spreadsheet structure and sample data set acid ionization constant parameters pK., and pKd to be input for this this problem. This exercise took the student through the sample data set. Four additional pages of text give the theory of the data analysis and the step-by-step instructions operations of entering headings, data, number formats, and formulas. The exercise concluded with the preparation for the preparation of this spreadsheet. The instructions of simple plots using the Excel Chart Wizard plotting utilfor the spreadsheet preparation can be followed without ity and final printout of plot and spreadsheet. much comprehension of this theory. The final values, which appear in column K when the student successfully completes the spreadsheet with the Grading given sample data set, should agree with the answers given to the student in column L of Figure 2. This allows This class tutorial was far sunenor than Drovidine suthe student to check for the successful completion of the perdetailed step-by-stepwritten hrections fo; these operas~readsheetsetuu and s a m ~ l edata set entrv. Anv distions. This "ex~eriment" was maded as a t as sf fail exercise: k e m e n t between the re& in column K A d t6e anthe submissidn of a printo$ of the correctly completed swers given in column L of the handout alerts the student spreadsheet and a titration curve plot constitutes a passof some mistakeb) that must be corrected. Help is availing grade. able. if needed bv the student. The submission of a ~ r i n t o u of t the correct. comuleted Scholastic Honesty spreadsheet and an asskiated plot of pH vs. the baielacid mole ratio for the samule data set constitutes a as sine grade for the exercise' of this second week. A I of ~ the We recomize risk that some students mav easilv -~~~ - - - ~ -the ~ spreadsheet operations have been illustrated in the tutocopy the spreadsheet of a willing colleague at this point or rial of the first week. In this second-week exercise the stuuse the s~readsheetof a colleame with their own data in dents are preparing a spreadsheet to be used in the third later experiments. We try to minimize this risk by not week for analysis of their own laboratory data. There are grading the spreadsheet per se. We have also made copynonstandard aspects to the calculations that deserve coming of another person's spreadsheet a violation of the colment here, alth;)ugh t h ~ huck~~ound s 1s not nveded hy the lege honor code. However, the same problem really exists student in ord(,r to follow the dlrectlons fix the cxcrclsc of with any mode of laboratory calculations;in an elementary creating the spreadsheet. laboratory problem it is almost impossible to prevent one student from simply copying the calculational methods of another student, substituting only different data. The stuComparison of Methods dents understand that the methods learned in these first In this calculation, as in the analysis of more complex exercises will be needed to c a m out the calculations later titration experiments camed out a t a higher level in our in the laboratory program and may be generally useful in cumculum, we have followed the approach of Waser (2) other courses. ~
~
~
Volume 71 Number 11 November 1994
935
3 4 5
6
10 11 12
Vb, mL 0.00 3.00
pKal 1.68 Kal
pKa2 5.91 Ka2
Residual
PH 1.82 1.85
Vtot, L nblna
[HI
[HA] [A]
1 [OH] nb, calcina
.
.
error error, sq answer 4.63e-02 8.34e-03
Figure 2. Strong-baselweak-dibasic acid spreadsheet structure and sample data set. and Butler (31, more recently redescribed by de Levie (41, in the exact treatment of titration calculations involving multiple equilibria. Here we use the pH a s the independent variable. The baselacid mole ratio or ndn, becomes the dependent variable to be calculated simply from the input values for the pK. parameters and the pH for each ~ o i n in t the titration. This calculated or theoretical value'for the ratio ndn, can he compared to the experimental or observed value for this quantity obtained from the ratio of moles of standard base added to the moles of the weak dibasic acid in the orirrinal s a m ~ l eat the chosen DH va!ue. This approach is particularly well-suited to spreadsheet use. I t is computationally much simpler to set up than the method described bv Breneman and Parker (5)in which the mole ratio ndn, is treated as the independent variable. In their method a fourth-order ~olvnomialeauation must be solved to find the calculated value for comparison with the experimental pH value for each value of the ndn. ratio. With the method of Breneman and Parker, optimization of the pK. values would minimize the discrepancy between the calculated and observed pH values for eachndn, ratio in the titration data set. In contrast to this, our calculation optimizes the pK, values by minimizing the discrep-
936
Journal of Chemical Education
ancies between the calculated and experimental ndn, ratios for pH values in the data set. Calculations
-
The instructions eiven to the student with this second spreadsheet exercise include the derivations and formulas for the calculation of the molecular weieht of the acid and then n., the number of moles of acid i i the sample. The calculations from this titration data of the total solution volume V,,, the experimental ratio ndn., the [H'], and the [OH-] in columns C, D, E, and H are then simple. The following equations provide the basis for calculation of the entries in columns F, G, I, J,K, and in cell E7, using input values for the pK. parameters and the chosen pH value. The acid mass-balance equation is
This and the two acid-ionization equilibrium-constant expressions combine to give the values for the concentration of the monoprotic acid form.
This concentration is computed in column F for each value of pH. The concentration of the deprotonated form, [A2-], in column G is then obtained from the second-ionization constant-equilibrium expression and the values in columns E and F. The theoretical value for the ratio ndn, in column I is obtained by substituting the results from previous columns into the charge-balance equation.
The ratio of this nb value to the n, value is entered in column I as the calculated ratio ndn,. The values for the ratio ndn, in column D are not dependent on the pK. values assumed. However, the values for this ratio in column I are dependent on these parameters. column J shows the difference between the theoretical value and the experimental value for the ratio ndnafound in columns I a n d b . Column K i s the square of this error in column J. The value that is computed in cell E7 under the label Residual is simply the sum of the squares of the errors in column K using data before the endpoint volume. The best values of the pK, parameters will be those that minimize this residual in cell E l , although this is not discussed a t the time the spreadsheet is prepared in this exercise. Titration of an Unknown In the third week of the laboratory program, each student is given a sample of an unknown solid, weak, dibasic acid and nerforms a oH titration usinn.. standard base. The student then emcrs this data into the spreadsheet in place of the sample data set. fillinn down the fi~rmulnsto include new rows of data. ~ h e s t u d e nis t instructed in the choice of the endpoint volume VOnafrom the experimental data set and in the .;t.lectinn of t h e m m l estimbte.; for the pK,, and oK.* villucs from the experimt~ntalpti values at the titraiionvolumes Vend/4a n d a t Vend x (374). Instructions are also &en on systematic variation of the pK, values to find t h e set that minimizes the residual sum of errors in cell E l . This comprises a brute force leastsquares refinement of the two & parameter values using the titration data obtained by the individual student. This feature of the calculations would clearlv not be ~ossiblefor the students without the use of a spreadsheet. Dibasic ac~ d s such . a s malic acid. whose nK. values are so close that there is no significant'break ih the titration curve a t the first equivalence point, can readily be used. Our mode of calculation and refinement of the pK, parameters works anite well in such a case. 'I'he final report ibr the experiment consists of a pnntout of the soreadsheet showing the cxoerimentsl data set and the results for the best p& value;, a plot of the titration data, a table of residual values for the different pK. values tried in the pK, refinement process, and some answers to auxiliary questions. Assuming the previously prepared spreadsheet was used properly with the student's own data set, we base grades essentially on the quality of the laboratory work and the answers to the auxiliary qnestion. Other Applications
Spreadsheet use is practiced and extended in three other experiments in the term. The spectrophotometric de-
termination of the equilibrium constant for the formation of the FeSCN2+ complex in the fourth week of the term uses a soreadsheet for the data analvsis. The structure of the spr&dsheet is defined by giving
