edited by JAMES P.
BIRK
Extensive Student Use of a Spreadsheet in QuantitativeAnalysis Jerome L. Mullin' and Robert J. Eierman University of Wisconsin-Eau Claire, Eau Claire, WI 54701 Our analytical chemistry faculty have desired to allow our quantitative analysis students to tap the power of a microcomputer for calculations and data analysis, hut we have been unable to do so due to the limited experience of our students. We recently decided to take advantage of some of the new user-friendly yet powerful applications software to fulfill our wish. Electronic spreadsheets allow numbers, labels, equations, and predefined functions to be entered into cells in a twodimensional matrix. A spreadsheet allows rapid development of "programs" for data manipulation. In addition, contents of cells can be rapidly copied to other cells allowing the same calculation to he quickly carried out on all elements of a large data set. Spreadsheet programs also have extensive plotting capabilities allowing a variety of types of plots to be made using data contained in the matrix. A number of spreadsheet applications have appeared in this Journal ( I 5 ) , and a summary of spreadsheets and PC's has been published (6). We perceived a spreadsheet t o be a reasonable choice as a tool for use by our quantitative analysis students. The software allows students to manipulate and display their information with a relativelv small investment in learnina- how to program a computer. We have successfully used spreadsheet software for a number of lahoratorv and lecture aoolications in three sections of the quantitative analysis cou& containing 35,10, and 50students, respectively. The software we put to use is the Student Edition of Lotus 1-2-3 (7).Student editions of several other spreadsheets that could be used for this application are also available (8-10). The issues that we tound it necessary to deal with in implementing extensive spreadsheet use were: (1) student access, (2) student training, (3) applications, and (4) unforeseen difficulties arising during student use of the software. Each of these issues will be described in more detail below. Student Access and Student Tralnlng Student access t o comouters and software was a concern to us. We have two P C - & ~ computers with hard disks and the soreadsheet software available for analytical courses in the ~ e ~ a r t m e nThese t. are available during the day and some evenings. Students have access t o other microcomputers on campus during evenings and weekends and we were able to provide access t o the software by putting two copies of the spreadsheet on reserve in the campus library. These
can be checked out for 2 h throughout most of the week and on weekends. In addition, students can buy their own copies of the student edition of the soreadsheet either from the bookstore (typically $4045) or frbm other students in used form. We found that it is necessary to warn students who do buy their own copies to install the software before using it and to be careful to define the default disk drives so they know where the files they create are stored (one student "lost" several files onlv to "find" them later on the system disk). We feit a small amount of training aould allow most students to start using the spreadsheet software. They could then master the details during subsequent use. The training consisted of two l - h sessions presented during laboratory time. The first was a l-hlecture on the use of a PC, DOS, and
Table 1. DOS and Lotus 1-2-3 Commands Presented to Students Command DOS DATE TIME FORMAT CD/DireCtory/Subdirecbry/ COPY PathlfilenamePath1
123 Cursor Movement Formulas COPY GRAPH EDIT HELP @Funnions
@ STD
878
Journal of Chemical Education
En-
Entered on computer start-up Entered on computer start-up Used to initialize new disks Used by students to entertheir own subdirectory Used by students to copy files lo me hard disk Starts Lotus 1-2-3
LOWS
@ AVG
Corresponding auihw. Current address: University of New gland. Biddeford,ME 04005.
Explanation
Data Regression
Several commands for cursor movement from cell to cell A brief discussion of how to enter formulas Copy contents of e cell to another Cell Thm command reveals a men& ot commands ~ s e d to create p 01s Used to edit entries without retyping cell contents Displays online help These are functions supplied with Lotus 1-2-3 Calculates the average of a range Galcuiates the population standard deviation of a range. Valve must be changed to give the estimated standard deviation. Calculates linear regression statistics for a set of points
the basics of the snreadsheet. This lecture was eiven usine a computer with a iiquid crystal display monit; that allows projection of the monitor images with an overhead projector. The uses of all commands presented during the lecture were demonstrated, and a handout was provided describing each command. The DOS and spreadsheet commands that were presented are shown in Tahle 1. During the second 1-h session students were given a chance t o run the software and do the things that were described in the lecture, under the supervision of the instructor. In themost recent semester, students were taken to the minicomputer lab right from the start for a 2-h demonstrationfpractice session, eliminating the hour of pure lecture. so students could trv the commands as thev were beine demonstrated. This arrangement allowed coverage of the same material as before but saved time and was much more effective. Only students who were very intimidated by the computer required more training than this. This group comprised about 10% of the class, and two or three individual sessions on basic computer skills solved their problems. We try very hard not to frustrate students early on and to show them the advantages of computer use. Over the first 4 weeks of the semester the students were given three homework assignments and three laboratory assienments that reauired them to use a wide varietv of the fuktions and power of the software (see ~pplications).Several handouts were distributed describing additional features of the software or solutions to common problems. A handout binder was placed near the central course computer, as was the spreadsheet manual. Students were encouraged to work together to learn how t o use the software. We fie1 that carrying out several different types of assignments early in the semester forces students to learn the basics of computer use and hopefully to appreciate the power of the microcomputer. I t is significant to note that all 10 of the students in the second section and more than 50% of the students in the third section of our course purchased their own copy of the spreadsheet during the first month of the course. Appllcatlons In this section we will consider the laboratory and lecture
Table 2.
Represantallve Laboratory Appllcdlonsa
Experiment
Buret Calibration Gravimetrlc Analysis Acidlbase libation
R&X
tibation
Potentiometrlc fluoride
Absorption Spechometry
F~UDI~SCB~CB
Summary of Spreadsheet Applicalication Plot "a~tuaiV O I U ~ Bvs. " "volume read"; a simple, early exercise Calculations; Electronic r e p m s~bmission Plot titration curve: calculate first and second derivatives:. .lot derivatives and Gran plot: determine pK, from plot Plot titretion curve end derivatives; determine P s Plot experimental polnts: use regression function to determine best tit line: plot calibration line; use regression results lo calculate concentration of sample; mDdel ISE response based on Nernst equetion (seetext) Plot spectra (from Spec 20);plot abswbance vs. concamration data: use regression function to find calibration lines; plot calibration curves Plot "L" vs. "concentration"; use rwession function to obtain dibalim
wonsheet lile6 urmaining calculations. results. plols, and answers to any que~tlonsare saved to the designated hard disk. These liles conrtitvte the studem's iaboratoly repart.
applications of spreadsheets and discuss electronic suhmission of assignments and reports by students. Laboratory Applications
Our auantitative analvsis course includes several basic instrumental experiments as wellas the more traditional wet chemical methods. Tahle 2 summarizes how we have nut spreadsheet capabilities to use to complement our labor&rvprogram. .. Our goal in these applications is t o make the advantages of spreadsheet use obvious to students. Reoetitive calculations like making derivative curves of titration curves demonstrate the power of the spreadsheet clearly. The ease with which plots can be set up and manipulated is another obvious advantage. Students must he reminded that their responsibility in terms of evaluating the quality of the data is not relieved by the presence of the computer, e.g., the student must make decisions reeardine the reiection of outliers before carrying out a regression analysis. A good examole of the usefulness of the soreadsheet is the mod&ng of instrumental response functions. For example, when students have completed the fluoride determination. they use the spreadsheet t o model the response of the iod selective electrode (ISE) by rearranging the Nernst equation. The theoretical ISE response is calculated for different fluoride concentrations. A plot of theoretical response vs. concentration may then he generated. The effect of various parameters such as interferences present at different concentrations (if selectivity coefficients are known or invented) may he observed by including terms in the ISE response function. This plot is put on the same axes as the measured ISE response ailowing a comparison of ideal and actual response. Lecture Applications
At this noint in our introduction of soreadsheet use. we have concentrated more on laboratory than lecture applications. but we offer the followine as examoles of soreadsheet utility outside the laboratory.-we feel ihere is significant untapped potential for use of this software in the lecture setting. Homework problem sets were assigned early in the semester to force students to become familiar with spreadsheet commands, equation formatting, and submission of worksheet files either on floppy disks or onto a hard disk. As mentioned earlier, a good way to convince students of the power of the spreadsheet is to assign them conceptually straightforward but repetitious calculations that are made simole hv" the soreadsheet. An examole is the calculation of a Gaussian distribution using a large data set. Of even greater potential is the use of the spreadsheet as a mechanism for introducing the concept of mathematical is to have modeline. One examole of such an aodication .. students generate stmng-base titration curves for a series of weak acids. The calculations for each acid are the same once the equilibrium expressions are rearranged. The tedium of this assignment is greatly reduced by spreadsheet use hopefully allowing students to grasp the principles more readily. Another exercise is to model the response of an instrument. This can he done with and without terms for interference or instrumental nonidealities. An example is the creation of two Beer's law plots, one with stray light terms and one without. Complex equilibrium problems can he handled well using spreadsheets. Because spreadsheets are not effective for aleehraic rearraneements, i t is necessarv to do such work on paper and then-enter the equations into the spreadsheet. The advantage of the spreadsheet is that the subsequent calculations can be repeated under different conditionsconveniently. Ry taking advantage of the spreadsheet. students
.
Volume 67 Number 10 October 1990
879
can gain a greater appreciation for the relationships in a comolex euuilibrium system without becoming bogged down in the algebra. For example, consider the determination of the concentrations of the various forms of a polyprotic acid as a function of pH. Once the equilibria are solved on paper, a template worksheet could he set up containing the solved eouilihria such that different DH values could be entered = and used to solve for the sought-after concentrations. Such an exercise would allow the student to see auicklv how the pH affects the composition of the system by looking at a number of examoles. . . all derived from the one solution to the original equilibrium prohlem. There are many other modeline exercise3 that are made practical when students are able t o k e a spreadsheet. Electronic Report Submission
We have also introduced a version of electronic mail along with the soreadsheet use. Students submit assienments and laboratory reports in the form of spreadsheet Tiles that are copied to the hard disk on a designated microcomputer in the analytical laboratory or contained on "hand-in" floppy disks. The hard disk is set up with a directory for the course, and each student has his or her own subdirectory within the course directory. Students create their report files on any computer and then hand them in by copying the spreadsheet files to the hard disk in the laboratory. The report files contain all raw data, calculations, results, conclusions, and comments. All pertinent plots are set up and saved with the spreadsheet. Once suhmitted, the report file may he retrieved by the instructor for evaluation. Grades and comments can he written into the worksheet, which is then resaved and can he copied back to the student's floppy disk. mechanism for the We believe that this is an aopro~riate .. introduction of electronic information transfer, which is important given the growing importance of electronic communication in the workplace. This approach has worked quite well with our small section (10 students). In the 50-student section there were some tie-ups when lab reports were due, hut otherwise no problems were encountered. I t eliminates the need for students to produce and submit hard copies of their worksheets. I t also eliminates the necessity of students printing out plots, which can he a problem for beginning computer users because their version of the spreadsheet must he installed correctly for the computer system and printer they are using. In addition printing plots on a dot matrix printer is somewhat time-consuming. A disadvantage of this system is that security is difficult to maintain. Lotus files can he protected with a password, which would be needed by the grader to access the file. If security is a real concern, files can be suhmitted on floppy disks or hard copy. Another disadvantage is that novice comouter users occasionallv mis~lacefiles during the transfer tb the hard disk. In addition, sufficient hard'disk space must he available to accommodate all of the students' files. DlfflcuItIeswlth the Spreadsheet Many unforeseen difficulties arose as we implemented extensive student use of spreadsheets. The following is a discussion of these difficulties and our methods of handling them. The first orohlem that was encountered was that students d o n o t i n i t k l y use the computer as a tool. Instead, they comolete their work bv hand and then no to the c o m ~ u t e to r type' in the results. +his results in tKe computer being a superfluous step in the prohlem-solving process. In order to coax them t o use the computer during the problem-solving sten. assienments were made that essentially could not he done witlout the computer (such as calculating a 160-point Gaussian distrihution). This helped rhem to get used to sitting a t the keyboard with thei; hooks t o do the work. I t should he noted a t this time that a spreadsheet is not a good 880
Journal of Chemical Education
tool for solvine (rearraneine) Students must he " - eauations. instructed to rearrange their equations on paper and enter only the final version into the spreadsheet for calculation. The next prohlem that became apparent is that entering mathematical ex~ressionsinto the spreadsheet in an acceptable format is difficult for many students. The two main formatting prohlems are multiplication and exponential notation. For multiplication, the "*" symbol is used in place of the"X"symhol. Another prohlemis that two terms in parentheses require a "*" between them to be multiplied by the spreadsheet. For exponential notation the "E" symbol re&aces "~10-",e.g., 6.022 E 23 (this is less of a problem since Lotus, at least, does acknowledge the former format). Our solution to these prohlems was to provide a handout sheet descrihing correct formatting and letting experience force thestudents toeet i t right. For some of the early assignments students had t i e correct answers availahle, so they could recoenize when their equation formats were right. h a related matter some students entered their equations to do the assirned calculations and then typed lahel versions of the same equations, which are visible on the screen regardless of the cursor position. This was done as a convenience for the grader. We decided to discourage this practice and just have the students enter their equations for the calculations. This means that the equation for a particular calculation is visible only when the cursor is in the cell containing the equation. Grading the worksheets required moving the cursor to each of the cells to view the equations, hut this was not a significant inconvenience. Significant figures pose a more difficult prohlem in the spreadsheet, hut they too can be dealt with. The worksheet has a default format for display of numerical values. This format displays up to eight digits, and, if the value is larger or smaller than what can he seen directly, the format is automatically changed to exponential notation. The column width can also he changed, which allows more digits to he displayed. Without redefinition of the column width, values in scientific notation can have onlv one or two significant figures. Calculation results and n"merica1 values-are displayed with that format with no regard to significant figures. I t is necessary to use the Range Format function to make the spreadsheet display calculation results with correct significant figures. This function is easy to use and is invoked after entering each formula. This situation is no worse than it is when doing the calculations by hand, hut unfortunately it is no better, either. Perhaps advances in equipment and software will make programs such as these able to deal with significant figures properly in the future. The onlv asnect of the GRAPH functions that caused significantconfusion was thedifference between NAME and SAVE. Naming-a plot . that has been defined with the graphics commands stores the plot definition parameters-al&g with the worksheet. If the plot is redefined, the named plot can he recalled later, provided the student has remembered t o save the worksheet file after naming the plot. Saving a plot creates a disk file that is used by the PrintGraph portion of the spreadsheet to print out the plot on a orinter. Students who save a plot instead of naming i t and then redefine the plot, thinking the original is named, lose the orieinal d o t . Handouts were ~rovidedreminding students of what naming and saving entail. Later in the semester students were taught how to use PrintGraph, and the confusion disappeared: When using ~ r i n t ~ r a p h ;is i thelpful to remind students to look a t the settings on the summary screen before trying to print graphs to make sure they have the proper directory and hardware settings. One other minor difficulty that appeared is that it is extremely difficult to do accurate extrapolations of data on plots displayed on compurer monitors. This is because of the curved surface of most monitors and the parallax associated with the glass screen. Students need to be taught to use mathematical functions to derive information from plots. ~
~
-
The final oroblem to be mentioned is one that caueht us by surprise. In an attempt to encourage student use of the computer and t o enable the instructor to help students with the computer, we made the computer available to the students in the laboratory during laboratory time. The result was that students spent excessive laboratory time working on the computer and then had trouble finishing the assimed experimen&. T o eliminate this problem, the
