the computer bulletin board Literature Cited 1. 2. 3. 4.
5. 6. 7. 8. 9. 10. 11.
P i o w F J. ChmL Educ. 1991,6s,A28%A2&?.
C m i e , J. 0.;Whiteley, R. V. Jr J. C k m . Ed=. 1991, 68, 921-926 David, C. W J Chem. Edue 1989,66,A237-,4238. Van Houten, J.J . Chem. Educ 1988,6J,A31bA315. Cae, D. A. J. Chem. Educ 1987, M , 496497. Mullin, J. L.; Eiermsn, R. J.J. C h . Educ 1990,67,878-881. Rosenthal J.J C k m . Edur 1991,68,A285-A286. Prais.M.0. J . Chem.Edue. 1992.69.488-493, Sparmw, 0. J Chom. Edue 1985.62.139-140. Laquer, F C.J. Cham Edur 1990,67,900-902. &chi, 0. I.Am. Lob. 1967,19,82-95.
Introducing Spreadsheet Data Analysis In the First-Semester Laboratory Michelle M. Bushey Trinity University 715 Stadium Drive San Antonio, TX 78212 Numerous approaches to the use of spreadsheets in chemistry courses have appeared in this Journal (Id). Some approaches have been geared specifically to the firstyear laboratory (6, 7).Recently, this Journal published an article describing an experiment that involves massing a collection of in order to reproduce bad data for siatistical analysis (8). We use this same experiment in our introductory chemistry lab, which is analytical chemistry, in order to introduce statistical concepts about small and laree data sets as well as to introduce our students to the use of computers in general and spreadsheets specifically Tv~icallv.we have seven sections of 2.532 students enrolfeh in tl& lab class. Most ofthese students are first year students. and for manv thid is their first colleee science laboratory. While m a 4 of these students are "computer literate", many have limited prior experience with computers. This lab is designed to introduce them to the lah, campus computer facilities, and to spreadsheets. As in the original article (81, students are given a set of 10 pennies each. Each penny set contains one "had data point". Nine pennies in each set are minted either prior to or a f k r 1982, and the 10th penny is from the other time period. Thus, the 10th penny will have a mass roughly 0.5 g different from the other nine pennies since the metal composition of pennies was changed in 1982.As in the original experiment, students perform a variety of statistical manipulations and tests on their individual data sets. They are told to perform a Q-test on any questionable data points and to use their own judgment in deciding if a particular data point should he discarded or not. Each student has one penny that fails the Q-test, but many choose to keep the data point based on the idea that after all, a penny is a penny. Finally, the students bring their data set, consisting of mint date and Dennv mass to the lab instructor and student teaching a h s t & (TA) for entry into Cricket Graph (version 1.3.li). The dates are entered into column 1 and the coin masses are entered into column 2. Data are rounded to the nearest 10 mg before entry. The magnitude of the standard deviation confvms that this is appropriate. Once the data have been entered, the frequency of data in column 2 is taken. Column 3 then consists of the masses (categories) and column 4 is the associated frequency values. The column 3 categories are then sorted ekher in ascending or descending order with column 4 as an associated coiumn. column; 1and 2, dates and masses, are then copied into columns 5 and 6, add the masses are sorted in either ascending or descending order with the column 5
-
-
A90
Journal of Chemical Education
dates associated. The entire class data set is entered into a single spreadsheet. Finally, the TA copies the spreadsheet into Excel (version 4.0) and labels the columns: dates, raw data, categories, freq. values, sorted dates, and sorted data. This explicit set of instructions is followed by the TA in order to prepare the large data set for student analysis. For those students unfamiliar with computers, it is important that the data set appear exactly as it is referred to in their handout. Each student then copies the data set onto their own disk for post lab analysis. The students are instructed to bring a new Macintosh formatted disk to lab for this purpose. We find that it is most convenient to prepare the data for the students in Cricket Graph and then to copy the data into Excel. However, any spreadsheet capable of counting frequency and sorting will be adequate for the lab. The students ultimately should receive the data in a form compatible with the spreadsheet most readily available on campus. Most Computer Services usually will he able to translate the file to another spreadsheet or another machine format (IBM compatible in our case) upon request. At this point, students are told that they have a series of statistical tests to perform on the class data and are iustructed that they must use a computer spreadsheet for this purpose. Students are told that they may use any spreadsheet and any type of computer, but instructions are only provided for Excel for the Macintosh as this is the available spreadsheet on the campus and department networks. This is facilitated by an explicit set of instructions for Excel. For the large data set, students are asked to report on the mean, midrange, standard deviation and 95% confidence limit, aided by computer analysis. This is compared to their own individual data set. Students then prepare a histogram of the data set and are led through a series of handout questions designed to lead them to the conclusion that the large data set consists of two populations. Students are then told to re-evaluate the data based on this conclusion and again to compare results with their small, individual data sets. Most students repeat the statistical analysis a t this point, treating the two data sets separately. Finally, printouts of the histograms and data analysis are requested. The following week, the instructor superimposes Gaussian curves over the class data and lectures briefly on "premeasurement assumptions" and the value of small versus large data sets. Explicit instructions for performing the required data manipulations in Excel are provided from the point of turning on the computer to plotting and printing in Excel. These instructions were written for a student with little or no previous computer experience. Some students require some extra help in getting started but all students are able to complete the assignment, although they do not all reach the desired conclusions. We currently are exploring the possibility of simply posting a class data set on the campus computer network. This would eliminate the need for every lab section to re-enter essentially the same data set. Students would then copy the class set onto their own disks directly from the network. Later in the semester students have two other opportunities to utilize computers and spreadsheets. Explicit instructions, which build upon the first computer lab instructions are again given. The first additional experiment involved determining the two end points of an acid-base titration by use of a Gran plot. In this case, students enter their own data and are given an equation and input in-
structions in order to generate the Gran plot values. They then print out the plot with appropriate grid lines so that they can determine manually the end points from the plots. Finallv. ",for the last lab of the semester. the students determine the amount of calcium in maple syrup by atomic absorption. A standard addition analysis is used and students are again given explicit instructions on how to utilize Excel for this purpose. In this lab, they obtain a computer generated least squares fit in order to determine the concentration of calcium in the svruo. At this ooint in the semester, all students are ablcto perform these manipulations quite easily. The purpose of the computer section of these labs is to familiarize the students with the campus computer facilities and spreadsheets, thereby ensuring a minimum level of computer competence for future chemistry courses. These labs achieve this goal. We will be using these formats for the third time during the Fall of 1993. Copies of the detailed instructions are available upon request. Acknowledgment I would like to thank Dennis Baltuskonis, John Burke, Jean Floyd, William Kurtin, Nancy Mills, Kraig Steffen, and Frank Walmsley, each ofwhom has taught a t least one section of this laboratory during the two years this experiment has been included. Special thanks is extended to Nancy Mills for donating the 320 pennies. Literature Cited 1. Brenernan, G.L.:Parker, 0. J. J C h m Edur 19% 69,46647. 2. Schruartz,L. M.J C h m .Educ 1892.69.879-883, 3.Rasenthal,J. J C h .Educ. 1891.68, A%-,4286. 4. Riom, F J Chm.Ed=. 1991,68,A282-A283. 5.Van R M , H. J. Chrm Edur 1991,68,A28&A285. 6. Edwards, P A; McKey, J. B.J Cham.Educ 1885,69,648450. 7 . Vitz. E.J Chem Edue 1892.69.744449. 8. Riehardaon.T H.J. C h m .Edvc 1991,68,310311.
Use of a Spreadsheet in an Undergraduate Physical Chemistry Laboratory Sally O'Connor and Dechelle Bailey Xavier University of Louisiana New Orleans, LA 70125
This uauer illustrates the use of a commercial spreadskeet program to assist physical chemistry students in oroeessine their raw exwrimental data into meanineful computed cgemical or physical properties. The deveiopment of the uroerams has been influenced in Dart by our desire to make our students more computer [iterate but more importantly by the need to help them understand how raw experimental data are converted into chemical results in many modern instruments. In our increasin~lv technologica~world,the students are exposed more frequently to instrumentation that are computerized. Labo~atory~instructors realize more and more-the need to address what can be called "black box syndrome." For examule. students can introduce a samule into an instrument:get printed results and have no idea what happened inside the instrument. By showing how a spreadsheet program can transform data, students are better able to understand what goes on inside the "black box." Presented .n pall at the 205tnMeel ng of the Amer can Chem~cal Soclety In the Dlv~s~on of Cnernlcal Education on Marcn 28,1993
Commercial spreadsheet programs such as LOTUS 1-2-3 and ASYST have been adapted for use in undergraduate laboratories (1-5).Our programs were developed on a PC computer using Microsoft Works, a multi-purpose software that allows our students to incorporate graphs directly into their word-processedlab report. Programs were develoued for six of the eight scheduled exueriments. two of which are described kelow. The impa& of the ;se of a soreadsheet in the Phvsical Chemistrv Lab a t Xavier Univ&sity is discussed. Bomb Calorimetry This experiment allows the student to determine the enthalpy of combustion of naphthalene from the rise in temperature of the water placed inside a Parr design bomb calorimeter (6).The program requires the student to input (1) the weights of the iron wire before p d aRer ignition, (2) the weight of the wire + pellet, and (3) the initial m d final temperatures of the water in the calorimeter.
The data obtained from the combustion of each pellet of either benzoic acid or naphthalene are entered. In our laboratory, we do one trial of benzoic acid and two trials of naphthalene. The spreadsheet then calculates the heat capacity of the calorimeter, the individual and average values of AU of combustion of naphthalene, and AH of combustion. Finallv. the uercentaee error of AH based on the published value'of enihalpy of-combustion of naphthaof the data lene is calculated. The student eets a orintout ' entered and the calculated reszts. Vapor Pressure of a Pure Liquid This experiment allows the student to determine the heat of vaporization of ethanol or water (7). The oromam requires the student to input applied pressures &d&eir corresponding boiling points. The spreadsheet uses these data to make a Clausius-Clapeymn plot of the l n p versus 1IT. A linear regression analysis of the data is performed, and the sloue. intercent and remession coefficient of the linear plot are report&. From tce slope and intercept, the enthalov and entroov of vauorization are calculated. The ~ t u d e n t " ~ eat s of ihitial data, calculated results, and a plot of his or her data. Programs for the other experiments were developed using the same approach. These include (a) the effect of temperature on %, experiment (8) to determine the enthalpy and entropy of dissociation ofbarium iodate, (b) the adiabatic expansion of gases (He and Coal experiment (7) to determine heat capacity ratios, (c) the bromination of acetone (8)to determine the reaction rate law, and (dl the conductance of electrolvtic solutions exueriment (8)to obtain the equilibriumco&tant for the dis$ociation of i weak acid. The uroerams eive a urintout of the raw data and Discussion In contrast to the non-traditional approach used in our general chemistry (9-10) and organic chemistry courses (11, 12), our physical chemistry courses a t Xavier are taught in a manner not unlike those taught a t major universities. A survey was given at the end of the Fall semester to the 36 students enrolled in the three sections of lab. Every respondent agreed that the use of spreadsheet benefited them. Some of the benefits commented on were (Continued on nertpoge)
Volume 71 Number 4 April 1994
A91
