edited by n. BATT Kenyon College Oambier. OH 43022
the computer bulletin boord
RUSSELL
Numerical Solutions for Oiprotic Acid Equilibria Using Spreadsheet Iterations
tool are demonstrated by the following considerations.
0. Jerry Parker and Gary L. Breneman Eastwn Washington University Cheney, WA 99004
(2)
Modern spreadsheet software for personal computers provides a very powerful tool for the research and educational chemist. Very complex chemical problems generated in the research and teaching environment can be solved in a simple manner with the use of modern spreadsheets that have options for treating circular logic or iterative procedures. Microsoft's Excel version 1.5 has the capability of processing algorithms that have formulas that depend on each other, providing the user with an easy means of solving simultaneous equations. All that is required of the user is the simple task of choosing "Calculation" from the "Options" menu and then checking the box labeled "Iteration". Unless specified, Excel stops iterating after completing 100 iterations or when all values of the algorithm change hetween iterations by less than the user-set value. This option allows the user to solve very difficult problems without the necessity of replicating a large number of cells for the purpose of producing the desired numher of iterations. Because of these advanced capabilities, the time investment for the user to develop a spreadsheet is reduced to a period of 10-15 min. The real time investment is a function of the complexity of the algorithm or mathematical solution and not very dependent upon the user's computer skills. Like all snreadsheets (14). . .. this software affords th;opportunities of solving the problem in an open format that displays any desired intermediate steps as well as the final results, which may be in numerical or graphic form. The user's logic can be easily verified by adding one or two steps that recalculate important constants and input data from the solution values. The numerical solution of the equilibria involved in the amphiprotic or intermediate species of a dipratic acid is a very difficult and time-consuming process. Spreadsheets reduce the time required to that of a short coffee break, and they require logic that is easily interpreted by Other chemists. The important aspect3 of this computational
+ A2HA- + H,O = H,A + OHHA' = H'
(1)
.
K,, = [Htl [HA-l/[H,Al (3)
[Ht]
+ [H,A] = [A2-] + [OH-]
combined mass and charge balance
This iterative numerical approach coupled with a simple spreadsheet technique produces the results far the diprotic acid, malie acid, resented in Table 1. It should he noted in Tahle 1 that the initial concentration or original estimate of HA- is reduced by 23.3 to 58.6% when the initial concentrationof HA- is within the range of 0.10 to 1.0 x 10W M. I t is clear from Tahle 1that hoth reactions represented by eqs 1 and 2 proceed to an extent that is nearly equal. The formation of A2- BS represented by eq 1 has a slieht edee over the formation of HIA as represented by eq 2, even with a disparity of 2.79 X 106-fold between the twoequilibrium constants. The acid dissociation reaction has Kaz = 8.00 X 10-6, and the base association reaction has Kbz = KWIKal= 2.87 X 10-". In this complex situation, the water equilibrium is sufficient to remove a significant and equal amount of hoth Ht from the dissoeiation reaction and OHfrom the association reaction. The net consequence of this reduction in product species for hoth reactions is an equal and significant shift in the equilibrium positions of hoth the dissociation and association reactions tuward formation of producLs.. The usual approximation for the pH of amuhiorotic s ~ e r i e ~redicrs s a value of4.28. v
The solution of these simultaneous equations produces an equation that calculates the hydrogen ion concentration for the syjtem a t equilibrium. This result is presented as eq 5.
Equstion5does not involveany approaimationa except those resulting from the use of the concentration (in plare of activity) of the amphiprotie species and hydrogen ion. A critical feature in the exact calculation of the hydrogen ion concentration is the means of obtaining the concentration of the species HA-. If the process involves iterations or circular logic, then a new concentration of HA- can be produced for each cycle of the calculation. The concentration of HA- is calculated from eq 6 in conjunction with eqs 3 and 4, which are also corrected by eq 6 during each cycle of the calculation.
u
PH = (PK,,
+ PK&
(7)
Initial concentrations of 0.01 M or higher are in agreement with this value. Calculations presented later in this paper will demonstrate that this approximate relationship does not hold for amphiprotie species that (Continued on page A6)
Table 1. Equlllbrlum ofthe Amphlprotlc Specles ot Mallc Add* HA' IHA-l~ntt
[HI]
[HA]
Volume 67
[A2-]
converted
Number 1 January 1990
pH
A5
the computer bulletin board have pK. values close to pK,. A severe test of the com~utatianalintemitv of this iterative procedure is furnished by the results for dimethylglyoxime, whirh are presented in
- .
.-".--.
Table 3 presents the values of p H as calculated by rhe spreadsheet method. These values are within the limits imposed by the experimental nature of the equilibrikconstants for the reactions that are considered. The dimethylglyoxime amphiprotic species deviation in DH. and in results in - the ~ - Lareest ~ this case the hydrogen ion concentration varies by 44%. In all of the remaining cases the deviation is not as large. The construction of the spreadsheet involves a small number of steps and very simple logic for developing the functions needed in each cell. The form of the spreadsheet is shown in Figure 1,and the functions that calculate the values of each cell are shown in Figure 2. The formal concentration or original estimate of the amphiprotic species is represented by F. Cell B7 represents [Ht1 and the function that calculates this value depends on the values of cells C7 and D7 which represent [&A] and [A2-], respectively. The function in cell B7 requires the square r w t as a result of the calculations. A function that uses the square r w t represents a severe test of an iterative method as it is possible to create a negative value, which is undefmed. It is possible to arrive a t an impasse (labeled as #NUM!) if the value of F is very small for the first calculation. This results from the procedure used by Excel version 1.5, which uses an initialized value of zero for cells that have not been evaluated but whose values are needed for the calculation of the current value of the cell being evaluated by Excel. This is only true for the first pass through ceUs that are functionally dependent on values in other cells, as all future calculations begin with the oresenl values of all of the cells at that time. This feature can be used to successfully aporoaehe discontinuitvof the functions or an Lndefined point. ~ h l c h a n c e of s successfully calculating values for algorithms that have circular logic are substantially increased by using the manual calculation option of the spreadsheet and by making all changes in the necessary values before the calculations are commenced. The most important aspect of these simple spreadsheet techniques is the ease and speed with which the repetitious arithmetic is completed. By using spreadsheets, students in the third quarter of general chemistry and quantitative analysis are required to focus their attention on the chemistry of this situation, because the arithmetic details are handled by the computer and the spreadsheet. The present example has several surprises for those who have never considered the three eauilibria and the mass and eharee halanee &at are involved in svstems of this nature. ~ a p i dcalculations p;ovided by the spreadaheet will permanently establish for the student the concept that the association and disswiation reactions proceed to an extent that is nearly equal. The hydrogen ion concentration or pHisnot very sensitive to the formal concentration of
-~~~ ~~~
~
~~~
A6
~~
-~
~~
~
~
~
. .
tion method can verify when approximate equations may provide reasonable answers and for which chemical snecies these values are valid.
the amphiprotic species as a result of this nearly equal shifting of these two reactions towanls~roduct.Further ex~lorationofthis chemicai system hy the spieadaheet-iteraTabk 2.
Equlllbrlum of the Amphlprotlc Speclea d Dlpmllc Aclds. Equlllbrlurn Concenbatlons
&
Add
DM0
lo-"
2.2 X
[H'I
Ku 1.
2.2 X
X
[HA
lo-"
lo-'
2.3 X
6.99 X 10-I 2.02 X 3.21 X lo-$ 1.12 X lo-' Fumarlc 6.85 X lo-' 5.04 X 10-a 1.11 X 9.12 X BImbonBte 4.45 X lo-' 4.69 X 10-It ReswcIno1 5.0 X 10-10 8.7 X 1.0 X 1.6 X lo-' 6.5 X 0-Tartaric 9.20 X lo-' 4.31 X 10W5 1.30 X lo-' 9.57 X 1 0 F 2.26 X Glutsrlc 4.6 X 3.7 X lo-' 1.3 X 10V 1.7 X lo-' 1.9 X Lyslne 8.5 X lo-'" 2.0 X 1.66 X 1.52 X lo-' 9.12 X InHial mncenmtion01 ha amphipdic species fm all ha dipeic acids is 0.0010 M. ' 6 fc" iyrine. =Kd 1m lyrlne.
lo-S lo-' 10-' 10F
lo-' lo-' lo-$
50.7 29.2 2.0 22.6 32.1 36.2 24.3
Table 3. Spreadshe4 CaICulatad pH ot Amphlprmk SpOCleS'
DM
10.66 3.95 6.30 9.98 3.89 4.90 9.78"
Fumaric Bicarbonate
ResorcItwI 0-Tartaric Glutark Lyslne
1o.m 3.053 6.352 9.30 3.036 4.34 9.08*
12.0 4.494 10.329 11.06 4.366 5.43 10.69
11.3 3.77 6.34 10.18 3.70 4.69 9.89
'innid mncwmatlon01 ha amphipmtic species is 0.0010 M. OpH is ~alcvlated by h.rproadsheet memod. "pH is calculated as (p&, pKd12.
+
6ror lyrine.
'&la lyrine.
1 2 3 4 5
I
I
B I C Diprotic Acid MaIIc
A 3.46E-4 8.00E-6
6 [HA-I initial 7
0.001
I
I
D
= Kal = Ka2
1.00E-14
[H+I 4.374E-5
WAI 9.604E-5
=
I
E
Equilibrium of HA-
F
Kw
-
HA-
[ ~2 ]
convened
1.398E-4
23.5%
pH 4.36
Figwe 1. spreadsheel uaedto calculate me equlllbrlurn mncenbatlons of all species. A
I
B
Dlprotlc Add
Mallc
~
Journal of Chemical Education
4.6 X
% HAcmverted
[A2-]
='[A2-1' 7 - 0 7 ) l K a l .KaZ'(F-C7-D7)/B7
Equlllbrlum of
HA-
="HA-" Converted 4C7tD7)IF
pH =-LOGlO(B7)
F@re 2. Spreadsheet wlth tha fmulas diwlayed. (Continued on page A8)
the computer bulletin b o d Modnied Gran plots of Very Weak Acids On A Spreadsheet F. T. Chau. H. K. Tse. and F. L. Cheng Department of Applied BiolDgy and Chemical Technology Hong Kong Polyiechnfc Hung Horn. Hong Kang
~h~ G~~ plot methodo~ogyhas heen utilized very of+.en for analyses of acid-base titration curves. For the titration of a weak HA with a strong base M+OH-, the variation in the hydrogen ion concentration of the resulting solution with the volume of titrant added can be expressed (7, ~10-pH= K&E - u)
values. What students need to do is to input the volumes of the acidic solution, the volumes of sodium hvdroxide added. and the corresponding pH readings in specified ranges of cells. The outputs from the program are the optimal value of pKw, the eorresponding correlation coefficient, WE,and pK,. Students can also make use of the graphics feature provided by the spreadsheet to visualize the fitting between the optimized Gran plot and the experimental data.TheprogramMODGRANwasa~~lied
to titrations of phenol with calculated values PK,, agreeing well with the literature 'IUe We have a listing of the Program and a description of the experiments. (Send requests to FTC.)
(n.
(1)
with UE and K. being the equivalence volume and the dissociation constant of the acid, respectively. A plot of the product ulO-pH against u gives the Gran plot. However, for very weak acids with pK, > 6, uc instead of u should be used in eq 1with
togive linear plots (9).In the expression, CB, Kw, and VT denote the concentration of the base, the autoprotolysis constant of water, and the total acid volume, respectively. As nart of our Chemical Technolm Laboratory course, we require the seco;;d-year students to determine concentrations and dissociation constants of weak acids. The experiment involves titrations of ethanoic acid and phenol with sodium hydroxide solution. For ethanoic acid, values of ulO-pH are plotted against the volume parameter u. ~h'e~uival&cevolumeas wellas the dissociation constant can he determined respectivelvfrom theinterce~twdtheslooeofthe p l a t . - ~ h ecalculation >an further be facilitnted with the use of a Linear reeression ~~~~analysis by employingacalrulator or a n m puter program packagesurh as Lotus 1-2-3. As for the phenol titration, the parameter Kw as given in the modified Gran plot formula (eq 2) varies with experimental conditions and enters in the equation in a nonlinear way. The figure shows modified Gran d o t s of a ohenol titration with different pKw values. To interpret data obtained, students have to usedifferent pKw values to dpduee u~ and pK, fur thc titration. It is tedious and time-consuming in so doing. Thus a computer program MODGRAN was written in the Lotus 1-2-3 environment to assist students in this type of calculation. The correlation coefficient of a set of titration data, uc and ~ ~ 1 0 - p in H ,a modified Gran plot varies monotonically with pKw around the value of 14. Hence, the dichotomous search (10) was employed by varying pKw systematically within a search interval. The one that gives the highest correlation coefficient is considered to give the optimal pKw value from which the quantities UE and pK. are deduced. In the program, the search interval, the tolerance for pKw, and the numbers of cycles are all set to have certain ~~~~
.~
A8
Journal of Chemical Education
~~~~
~
~~
~
Modified Grsn plots of a phenol tltration with DKW valuesot 13 25, 13.75. 13.89. 1400,and 14.45for curves A-E, respenlvely.
~
Computerizinga Stockroom Inventory Martha M. Vdllng SUNY College at Brackport BrockpoR, NY 14420
We have acquired, like many schools, rather more chemicals and supplies than any one of us knew about. Furthermore, our collection is spread over multiple rooms, several buildings, and many different-sized containers. When we decided to computerize our holdings, our gods were to find out what we owned, to save money, to improve storage conditions, to promote safety, and to eliminate unnecessary holdings. This report contains a series of recommendations for others scartina rheaame task. Unlike Fehu ( 1 1 ) . westrongly suggest usine rommercial database software. There is noreason to reinvent the wheel. The data-
A. Identifying 1. h g m i c chcmicalr a) Chemical Absuacts Service Rcgiatry Number b) Prefix C ) Name (wudly thc a m c on !he confsinu) 2. Inorganic Chemicals a) Chcmical Abstracts Servicc Registry Numbcr b) N m c (usually the n m c of the container) E) Formula 3. Glaaswarc a) GIasawm number (for the canpvrcr lo urn, I d l y generated) b) Name C) Size and other spsdficrtions 4. N o h w a) Notion number (for Ulc computer to use, l o ~ a l l yp n m k d ) b) Name (description given h u e ) 8. Spscific 1. Quantity 2. Unit 3. NcwlOpcncd 4. Msnmfsctumr 5. catalog N u m k 6. Building 7. Room 8. Cabinet 9. Shelf 10. Dcpartmcot 11. unit price 12. o a k 13. Reorder level
A.
Selccl D a b a r c to Wor* On: I. Orgsniea 2. loorganics 3. Glnra 4. Notion8 Enter your choice: 2
B.
You may apccify the record you want by sntcring I. l%e CAS Numher 2. The Chemical's Nunc 3. Ita L m n i o n Enter your cboics: 2 Hcr. is the string you gcocrsted: Is this ok? I h f a u l l = yes1
C.
LITHIUM
D. CAS N u m k 1. 16853-85-3 2. 755035.8 3. 554-13-2 4. 7447-41-8 5. 7790-96-4 6. 10371-48-7
Tocal Records Found =
6
Chemical N m e Lilhivm Aluminum Hydr Lithium Bromide Lithium Carbonus LiUltum C h l a i d e Lithium Nitrate Lithium Sulfate
Fmuls LIALH4 LBR LIZCO3
ua
LU2S04.H20 INO~
1. CASII: 7550-35-8 2. Parmula: U B R 3. Name: L17HIUM BROMIDE
hasa should be ahle to generate menus and rewrts. No matter how earefullv vou set un
your records, your spedicationS&llchsngk as you enter data. Choose a database whose structure can be changed when data are in it and one that can be expanded. As scan as the utility of the database hecomes apparent, all mrts of other chemieala and supplies will be added to it. Seven years ago, we started our inventory with Heneo INFO', a relational database that was installed on the c o l l ~ emainframe. Since then. the mainf m ; haa been upgraded severi times. Two yeare ago, we switched from an old vernion of LNM to a new version of Prime INFORMATIOW and gained hetter menuing and searchfunctiona Once ow system was operational in the stockroom, all of the biology department's chemicals were added to it. Thin was not anticipated a t the outset, hut it did not cause any problems as the software handles expansion easily. Personal cornputera and skweiated software have improved dramatically since we starbd our inventory. At this time a personal computer with a dedicated hard disk could he used to inventory a stockroom. Even a mall stduoom is going to fill floppy disks quickly, and disk swapping is not faat. The hard disk should he easily backed up. as entering the data takea toolong to risk losing it. Our chemicals and suppliea fall into four categories: organic chemicals, inorganic chemieala, glassware, and notions (everything &). By far the largest category in term# of numbers of different items is or&c chemieala. For each item we own. we ,
=
~
~
.~~~~~~ .
item h& identifying iniAatioo that does ~
~
~~~~
not change and specific information that does change. F i e 1 lists the descriptors we use. Our 2-propanol holdings are deaerihed as follows: Organic Chemicals File I d e n t i i Descriptors CAS#: 67-63-0 Prefi: Name: isopropyl alcohol (this is the
4.
5.
QTY UNIT N/O M k CAT# BLDG RM CAB SLF Rice DakDEFT RO 3 ILB N Rshcr LI17 Smith 16 23 Cl C 1 1LB 0 FMs L I I 7 Smith 16 26 El C Change which itcm (or enter A
rn add items):
name two suppliers uae on their bottles) Specific Descriptors Quanity 1 Unit: 20L Newlopen: 0 Manufactwer: Baker Catalog N u m k 908047 Building: Smith Room: 16 Cabinet: 3 Shell: 2 Department: Chem. Unit Price: Date: Reorder Level:
12 500mL N Eastman Kodak 13031 Smith 16 2
3
8WmL N Aldrich 27,049-0 Lannon 200 1
4 Bio.
5
Chem.
We have 16 bottles of 2-propanol in three different-sized bottles in three different locations. We try to uae the chemical names on the containers even if they are not the "best" names as we often have nonchemists checkinginventory. The search wwnand in Prime INFORMATION fmda whole words or partial words. Thus asking for all the organic chemicals with "prop" as part of the name would pick up iaopropyl alcohol as well as l-propanol and 2-chloropropane. When the names on the bottles m n o t intuitive and to get m u n d the many names for one compound problem, we included Chemical Abstracb Registry Numhers in our database. ChemicalAbstracts Service M t r v Numhers are listed in manv eatal- a d can he found in the baccofthe ~ e r c kIndex. Figure 2 shows the contents of several Computer screens seen on the way to locat-
ing lithium bromide in ow department. Note that only the word lithium is used in the search. We indude the descriptor PREFIX in the organic database so thatreports of ow holdings can be alphabetized correctly. Organic chemists exvect to find 3-nitronhenolunder " " u notunder"3"or"m." * Moat of the i t e m in our ntaekroom were acquired before computerization. Thus most of the fields for unit price, date of acquisition, and reorder level are empty. We are f&g these fields only as we purchase ~~
~
,
' Henm Software Inc., 1 0 0 Flfm Avenue, Wald.lam. MA 02154. PRIME Computsr Iffi.,500 Old C a n n e c t l a r t
Pam,Framlngham. MA 01701.
Volume 67
(Continued on page AIO)
Number 1 January 1990
A0
the computer bulletin board items. For items like paper towels and Pasteur pipets, these fields are in active use. Themanufacturers and catalog numhers are veryimportant in this inventory. They allow the user to look up specifications, and they assist the purchasing officer. The new (unopened)/opened (old) descriptor is very helpful. We cannot afford to have, nor do we desire to have, stockroom personnel or faculty measuring and recording amounts in every bottle. We use a relational database to connect identifying descriptors (fields) with multiple specific descriptors (fields). The Chemical Abstracts Service Registry Numbers, the glassware numhers, and the nation numbers relate (connect) the identifying fields with the specific fields. These numhers will be the bar codes for all items in the stockroom if we decide to go that way. We have decided to leave identifying fields in our database even when we no longer have the item. Thus our inventory will show that we once had something but that we now do not. How useful this will he, we have not ascertained. It took about five years (part time) to computerize Broekport's Chemistry Department holdings. We have yet to put research chemicals (compounds generated in the lab or given to a researcher or commercial compounds acquired by researchers hefore computerization) and research supplies into the system. Please recognize that computerizing an inventory is a long-term project. It cannot be done in a month. In order
A10
Journal of Chemical Education
to he useful, the inventory must he checked an a regular basis. Once a year may he all we need. We have found that reports listing all the items allegedly in a specific cabinet and on a specific shelf greatly assist the process, because it is easier to check a list than to write out a new one. The actual handling of items is much more time-consuming than data entry on a computer. Computerization only changes handling times when it saves walking through buildings looking for a specific item. If inventory and ordering are in the job description of one person, then that person should be in charge of computer access. With chemical names, many people think their system is better than whatever system is being used. Lots of editors are not needed. At Brockport, stockroom users can view the data, hut only authorized persons can edit it. We strongly recommend that one person he in charge of the inventory. At first, as items were checked out of the stockroom, Brockport's computer stored the data in a file called CHECKOUT and added a date and code for who checked it out. The plan was to clear this file once a year. We have since learned that we should know the new location of items checked out, particularly for fire safety reasons, the rooma in which the chemicals are placed. We are currently modifying our program to do this. We are not modifying the basic database; we are just adding new types of reports to the menu. In summary, Brockport has a computerized inventory of its chemiealP and supplies. We now know what we have, and we have saved money (mostly by not ordering items we already own). We have not yet used our
newly organized information to improve storage conditions or to promote safety or to eliminate unnecessary items from the collection. The inventory should he useful when we start those johs.
Acknowledgment Aeleen Frisch designed the menus and transferred the data from Henco INFO to Prime INFORMATION. The author wishes to thank Aeleen Friseh, James E. Eilers, and Norma Plyter for sharing their computer expertise with her and to thank Louis W. Markiewiez, Timothy Good, Joseph Sedita, and Lynne Fullerton for data callection and entry and to thank Sandra Marks who is now in charge. The organic database was described at the 8th Biennial Conference on Chemical Education, University of Connecticut, Storrs, CT, August 8,1984.
Breneman,G.L. J. Chem.Educ. 1986.63.321-332. Levkov, J. S. J. Chem.Educ. 1987.64.31-33. Jmhi. B. D. J. Chem. Educ. 1987.64.790-791. Ca,D. A. J Cham. Educ. 1981.64,137-139. Coe, D. A. J. Chem. Educ. 1987.64.4W97. Van Houten, J. J. Chem. Edur. 1988.63, ,4314-A316. Harris,D.C. Qunntitotim Chamieoi Anolyala. 2nd ed.: Freeman: New York, 1987: pp 243-24L 6. Ch*", F. T. compv* Chrm.. in preas. 9. Sehwartz. L. M. J. Cham. Educ 1987.64.947-950. lo. Gottfried, 6. S.: Weisman. J. Introduction to Optimim i o n Theory; Prenfi-Hall: Englewood Cliffs, NJ, 1. 2. 3. 4. 5. 6. 7.
1973. 11. Feiiu,A. L.J. Chem.Edue. 1988,65,25.
