edited by JAMES P. BIRK Arizona State University Tempe, AZ 85281
computer series, 138 Spreadsheet Exercises for Instrumental Analysis Michael G. Prais Northern Illinois University, DeKalb, IL 60115 The importance of using spreadsheets in chemistry is due to their ability to present and manipulate numerical data. Applied algebra textbooks point out that there are three ways to present numbers. Digital information in a list, table, or roster Analog information as points on a graph Symbolic information as a formula, expression, or function The soreadsheet is caoable of all these t w e s of presentade'scribed a tion. &nee the term 'spreadsheetn origi;lly static. tabular d i s ~ l a vof numbers. the modern spreadsheet'might he better called a numerical ~reseniation package. The Assignments I n this article. five sureadsheet assienments are described. They ha;e beenised in a courseon instrumental analvsis to familiarize students with both personal compute& and the numerical analysis of data:Each assignment challenees students to use new spreadsheet operaexercises. For tions and ohjeds that build on the example, graphic presentation of data is required on all assignments after the first one. Abbreviated descriptions of the students' assignments are given in the following sections with boldface headings. Proiect SERAPHIM' provides steu-bv-step - - - instructions for completing the assignments and for copying the results as Lotus 1-2-3 files. Since there are several ways to produce the output shown in the fimres. some students mieht fail to use and learn some of k e mbre powerful sprea&heet techniques that do not affect the display of the values, such as name references and cell references. I t is important to check that each of these techniques are actually used. The Printouts As a matter of good practice, each spreadsheet is required to include a header with the date the student's identification a description of the calculation a list of important parameters
out to the students that having both printouts at hand can aid the process of checking assumptions and calculations. Also, the printouts can be kept to document the work and to recheck the calculations in the future. The instructor can also request a n electronic copy of the original spreadsheet to quickly examine values and formulas. The Steps Each assignment describes t h e construction of a spreadsheet in general terms in roughly 20 steps. Since the sequence of steps is listed for students, it is appropriate to ask students to explain why each step is important. Students are expected to describe (in a separate document prepared using a text-presentation package) the objects and operations used to affect these steps and to answer any questions posed. Orientation The students are encouraged to use instructional computing labs on campus if they do not otherwise have access to a personal computer with the appropriate software. The brand of personal computer, the brand of spreadsheet software, and the brand of text presentation software are not specified. A- two-hour introduction to the use of a uersonal computer and spreadsheets in an instructional Eomputing lab is provided for the students at the end of the first week of classes. The typical student with little or no experience with a spreadsheet needs about one week to complete each assignment. At the end of the course, most students voice confidence that they could and would use a numerical Dresentation package"to present their data from other projects. ~
~
Electrical Measurement Errors In this exercise students produce a table that describes the fractional voltage error made in measuring voltage from a constant-current source. The table should also describe the fractional current error made in measuring the current from a constant voltage source with a source resistance R, using a meter with resistance R,. The fractional voltage error is calculated as
Students are expected to submit two printed versions of their results: one that displays the values and another that displays formula% The student must expand the column widths to display all formulas, and they must decide where to break the display across the pages. The expanded printout is useful for more than just checking a student's use of the spreadsheet objects. I point
The fractional current error is calculated as
' Project SERAPHIM, Department of Chemistry, University of Wisconsin-Madison,1101 University Avenue, Madison, Wi 53706.
The key to avoiding measurement errors is to recognize the relationship between the meter and the source resistances in each of these situations.
488
Journal of Chemical Education
Rm
current error = ---
(R,+ R,)
Assignment 1. Electrical Measurement Errors Calculate the errors in electrical measurements. By changing the source resistance, illustrate the relations between source and meter resistances where the errors are the largest (fractionalerror: 0.001). Fractional Voltage Error = Rs/(Rs + Rm) Fractional Current Error = Rm/(Rs + Rrn) Parameters: Source Resistance = Base Meter Resistance = lncrementing Factor = Meter Resistance (Ohms)
RrnIRs
I.OE+Ol
1.OE-02
1000 ohms 10 ohms 10 times previous value Fractional Voltage Error 0.990
Fractional Current Error 0.01
quency that is lower than the actual frequency. for V&.I, > Every cycle of a n oscillation must be sampled a t least twice so that the measurement will recognize the actual frequency of the oscillation. The Nyquist frequency is the frequency below which measured frequencies are accurately recognized, that is, half the sampling frequency. The value of the function sin (2mta) oscillatina- a t frequency v is calculated a t a sequence of regularly spaced times ta for several values of the frequency. The calculation is equivalent to the sampling of this function. Examining the plot of the values allows one to count the number of peaks of the oscillating function during 1s and to calculate the apparent frequency of the oscillation for a variety of signal frequencies. The Nyquist Theorem is dramatically demonstrated by the similarity in the sequence of plots with signal frequencies above and below half the sampling frequency.
SpreadsheetLearning Objectives This assignment covers the following material:
Worksheet Learning Objectives This assignment covers the following basic operations that are required to set up a spreadsheet calculation: the isolatiun of impartlnt pnramrrcrs in a header the useof text labels t h e eon~truetionof formulas from algebraic operations the use of cell references a general method for the use of a formula thr cow oueratron to build a n incremmtine.list of values the useofihe copy operation to populate a Lble inserting a new column formatting the values for presentation
the use of descriptive name references that can be used in place of the cell references to make the formulas easier to understand the use of an additive increment that produces a simple algehraic sequence with evenly spaced values sthe calculation of an independent variahle sequence from this sequence the construction of a table in which each of several adjacent columns carries values that were calculated with a different value of the same para~eter the display of values with units the caleulation of precise values of mathematical constants, such as rr the use of intrinsic functions, such as the sine copying a formula into a black of cells rather than a column or row plotting and identifying a set of parametrized curves on the same graph
This assignment uses a multiplicative increment in the independent variable to produce exponential scaling. This A significant portion of a n example assignment is disstands in contrast to the other assignments that use an played in Assignment 2. additive increment. The multi~licativeincrement gives values that range over sev- r Assignment 2. Aliasing eral orders of magnitude, which are necessary in this exercise. Calculate the values of sin12 pi v t(k)],where v is the frequency of oscillation and t(k) is This assignment demonstrates the "what the time. if' potential of the spreadsheet by asking t ( k ) = k S T the student to change the value of the base Nyq~istshowed that s gna components wilh Ireq~enc~es greater than nalf tne meter resistance and its incrementing fac- sampl ng freq~ency are a iased lo otner ower lreq~ences. . . tor in the table. This produces a range of values. At one end of the range, the fracParameters: tional voltage error is large; a t the other Sampling frequency = 1 TT = 20.00 Hz end, the fractional current error is large. Sampling interval = T = 0.05 s The significant portion of a n example assignment is displayed in Assignment 1. pi() = 3.14159265 arccosine(-I)= 3.14159265 Aliasing Measurements of sin12 pi vt(k)]for various frequencies v I n this exercise students produce a table Measure- Measure- 1.00 Hz 8.00 Hz 9.00 Hz 10.00 Hz 11.00 Hz 12.00 Hz and a graph of a set of simple sine functions ment ment t h a t differ by frequency. They are calcuIndex k Time t(k) lated (sampled) a t specified intervals (frequencies) to show the phenomenon of aliasing. Aliasing occurs when a signal or the component of a signal of a particular frequency is sampled too infrequently. The resulting data then appears to oscillate with a fre-
Volume 69 Number 6 June 1992
489
Students also learn to print the values in the table to a disk file. The time of the measurement and the values from different frequencies for the same time of measurement appear on a single line separated by commas. This is a common format for transfering numerical information between programs. The values can be analyzed by a Fourier transform routine if one is available.
Analoa " Filter ReSDOnSe In this exercise students produce a table and graphs that desnihe the freauencv deoendence of the ratio of the output voltage to the i n p i t vatage (gain). This is done for simple and three-pole Butterworth low-pass filters.
Assignment 3. Analog Filter Response Calculate and plot the voltage gain as a functionof frequency for low-pass filters. Gain = VouWin = sqrt[l + (vlvcutoff)"2p]"(-1) Simple low-passfilter has p = 1 (numberof poles). Three-pole Butterworth lowpass filter has p = 3. Parameters: cutoff frequency = 1000.00 Hz Frequency Dependence of Low-Pass Filters Frequency (Hz)
The value v is the frequency in cycleds. The cutoff frequency vmbs for a simple low-pass filter, with a n in-line capacitance C and a path of resistance R to ground, is given by the reciprocal time constant
The value p is the number of poles. A simple filter bas a single pole. A low-pass filter can be used to eliminate signal components above the Nyquist frequency and to avoid aliasing when the remaining components are measured. The gains of the filters are plotted against frequency and the decimal logarithm of the gains are plotted against the decimal logarithm of frequency for cutoff frequencies of 1000 and 500 Hertz. The log-log graphs are called Bode plots. These graphs show that oscillations above the cutoff frequency are not eliminated: They still appear with a reduced amplitude. The graph also shows that the Butterworth filter shows a sharper reduction than the simple filter. Spreadsheet Learning Objectives This exercise wvers the following material. the use of the logarithm, power, and square root functions the differencebetween relative and absolute addressing t h e use of relative and absolute addressing in copying with adjustment of cell references the lack of adjustment to name references during mpies how inserting rows and columns affects the relative cell references and the absolute cell references the creation of related plots by selecting different mlumns of data the effectand eficiency of changing the parameters on the graphs presented The inherent flexibility of a spreadsheet is again demonstrated by requiring the students to adjust the range of the frequencies. Then the table and graphs will show the highgain region a t low frequencies, a s well a s the attenuation region a t high frequencies. The significant portion of an example assignment is displayed in Assignment 3.
Digital Smoothing I n this exercise students produce a table and graphs that describe how data can be smoothed with digital low-pass filtering moving-window averaging polynomial smoothing
.
Each of these techniques can be used to remove high-frequency components that typically constitute the noise in a signal.
490
Journal of Chemical Education
50 100 150 200
Log Simple (Freq) Lowpass Gain 1.70 2.00 2.18 2.30
0.98 0.95 0.93 0.91
Log (Gain)
-0.01 -0.02 -0.03 -0.04
Butterwotth Lowpass Gain 1O .O 1O .O 1.00 1.00
Log (Gain)
0.00 0.00 0.00 0.00
The following oscillating function, which is composed of a low-frequency term and a high-frequency term, is used to provide the data.
Students are asked to compare the following methods that are used to smooth a sequence of data. the commonly used moving-window average
the much-touted Savitsky-Galay methcd t h e vrover calculation of the smoothed data at all voints on the h & v a l Digital Filtering I n the first part of this assignment, the high-frequency components from the set of the data points (pklare removed by a recursive digital filtering algorithm to produce a set of filtered data points I q k 1.
where 0 < A < 0.5 The value k is the index of the measurements. The coefficient A is determined by the sampling interval T and the cutoff frequency vCubs.
The value v is a frequency given in cyclesls. Choosing A smaller than 0.5 will always make the cutoff frequency smaller than the sampling frequency ((rl). Moving-Window Average A second approach to removing high-frequency components from a set of the data points lpr I is to use a threepoint moving-window average to produce a set of averaged data points ( q k 1.
This i s the simplest smoothing algorithm-and the one most likely chosen by students. However, many students are not likely to treat the points a t the ends of the data set rationally. Since the data points that come before the first data point and after the last data point are not available, the previous formula cannot be used to calculate q, and q ~Linear . extrapolations based on the first and last two data points are used to predict pc.1, and p ~ which + ~are required for the calculation of q, and qN. The following formulas are produced by including the extrapolations in the equations for q, and q ~ .
and 4N =
+~ -PN-2 + ~PN-1
P N
6
Polynomial (Savitsky-Golay) Smoothing
A third method to remove high-frequency components from a set of the data points ipk1 uses five-point quadratic least-squares polynomial smoothing to produce a set of smoothed data points 1 qk I. -3~k-2+ 12pk-l+ 1 7 + ~12~k+1~ 3~k+2 Qk = 35 These equations are determined h m the least-squares fit of a quadratic to the set of five-point sequences in the data set. The first two and last two smoothed data points are calculated with the following equations, which use quadratic extrapolations to predict values for data points that are necessary but unavailable.
Assignment 4. Digital Smoothing illustrate three digital methods for smoothing data. Diaitai Low-Pass Filterina ~ & ~ i n d o w ~veragiig Least-Squares Polynomial Smoothing lnput data: p(k) = (516)sin[2 pi v t(k)] + (116)sin[2 pi lOv t(k)] t(k) = k ' T Digital Low-Pass Finering q(k)= A p(k) + (I-A) q(k-I) A = 2 pi vcutoff T / ( I + 2 pi vcutoff T) Moving-Window Averaging q(k)= [p(k+i)+ ~ ( k +) p(k-i)113 Five-point Ouadratic Polynomial Smoothing q(k)= [-3p(k+2)+ 12p(k+l)+ 17p(k)+ 12p(k-1) - 3p(k-2)1135 Equations for output at the endpoints of the data set are listed in the handout. PARAMETERS: 1O . O Hz Frequency = v = 0.001 s Sampling interval = T = 100.00 Hz Sampling frequency= 1/T= 15.00 Hz Cuttoff frequency = vcutoff = A= 0.49 Status: A < 0.5 is OK. Value Value Measure- Measure- lnput Value Averaged Smoothed men1 ment q(k) q(k) Index k Time t(k) Filtered
Value q(k)
~(k)
0 1
0.00 0.01
0.00 0.15
0.00 0.07
0.01 0.14
0.00 0.16
The suggested sampling frequency is 32 sampleds. Students are required to recognize that the cutoff frequency should be much larger than the sampling frequency.
Derivatives and Integrals I n this exercise students produce a table to compare numerical and symbolic differentiation and integration.
Determining the Errors Polynomial smoothing is often called Savitsky-Golay smoothing. The moving-window average is three-point linear least-squares polynomial smoothing.
Spreadsheet Learning Objectives This exercise demonstrates the following material: the use of extensive, complicated functions that include several values fmm the same column .the use of an "if" statement in a cell to notify the user of pertinent conditions how changing identified parameters can he used to examine a function The significant portion of a n example assignment is displayed in Assignment 4. The effect of changing the cutoff frequency of the recursive digital filtering algorithm is examined using cutoff frequencies of 15, 5, and 0.5 Hz. These cutoff frequencies will filter one of the following combinations in the input signal with frequencies of 1and 10 Hz: neither component one component both mrnpanents
Three numerical derivatives of a simple sine function
are compared to its symbolic derivative cos (2nvtk) to show the errors produced by each method. Then two numerical integrations of the cosine function
are compared to its symbolic integral
to show the errors produced by each method. The errors made by each of the numerical methods depend upon different powers of the sampling interval T. The functions are calculated a t a sequence of regularly spaced times tk = kT that are multiples of the sampling interval. Volume 69 Number 6 June 1992
491
Approximating the Derivatives
I n this exercise the integrals are calculated by accumulating the areas between data points rather than by acmmulating the weighted values of the data points that appear in the expressions for the integral over the whole interval. Optimal methods for calculating the derivative and the integral are shown in the spreadsheet using sampling frequencies of 20 and 40 Hz. These show the effect of the sampling frequency. The root mean square difference between the symbolic and numerical integrations is calculated to summarize the difference in methods.
The derivatives will be approximated three ways:
with a backward difference quotient
m
_P,(tk) - - p y t k ) dpk= @k -Pk-l) T
'with a centered differencequotient
with an expression calculated fmm the Taylor series expansions about five nearby points
Evaluating the Integral The trapezoid rule and Simpson's rule are used to evaluate the integral of the cosine function over several complete cycles. The integral of the cosine function is a difference of scaled sine functions, which is the value of a single function when the interval starts a t 0. The trapezoid rule approximates a n integral by calculating the area under a straight line between two adjacent data points. The contribution made by the area between these two points to the integral over the whole is given by the following expression.
Assignment 5. Derivatives and Integrals Calculate numerical derivatives of sin[2 pi v t(k)]/(2pi v), and compare them to the symbolic derivative: cos[2 pi v t(k)]. Backward differencequotient Centered differencequotient Dp(k) = [p(k+I)- p(k-I)11(2T DP(W = [PN- p(k-l)lrr Taylor series expansion Dp(k)= -[p(k+2)- 8p(k+l)+ 8p(k-1) - p(k-2)]/(12T) Calculate numerical integrals of cos[2 pi v t(k)]over (0, N'T) as a sum of two and three point contributions, and compare them to the symbolic integral: sin[2 pi v N'T]/(2 pi v). Trapezoid rule contribution S p(t)dt(k)= [p(k)+ p(k-l)](T/2) Simpson's rule contribution S p(t)dt(k)= [p(k)+ 4p(k-1)+ p(k-Z)](T/3) PARAMETERS:
RMS INTEGRATION DIFFERENCES:
Sampling frequency = 1 f l = 20.00 Hz Sampling interval = T = 0.05 seconds Sional freauencv = v = 2.00 Hz
Trapezoid Rule
0.00182
Simpson's Rule
0.00005
Aeasurement
Centered Difference
Index k The sum of these contributions over the interval (k = 0,k = N) is given by the following expression.
w=2piv
0 1 2 3
Measure- sin[w't(k)]/w Backward ment p(k) Difference lime t(k) 0.00 0.05 0.10 0.15
0.00 0.05 0.08 0.08
0.94 0.58 0.00
0.76 0.29 -0.29
Taylor cos[w't(k)] Series Expansion
0.31 -0.31
1.00 0.81 . 0.31 -0.31
TRAPEZOID RULE:
where .r is a n arbitrary value of t k . Simpson's rule approximates a n integral by calculating the area under a quadratic fit to three adjacent data points. The contribution of the area between the first and third points to the integral over the whole is given by the following expression.
The sum of these contributions over the interval (k = 0,k = N ) is given by the following expression.
492
Journal of Chemical Education
Measurement lndex k 0 t 2 3
Measure- cos[w't(k)] Individual Running sin[w't(k)]/w Difference ment p(k) Contribution Integral Squared Time t(k) 0.00 0.05 0.10 0.15
1.oo 0.81 0.31 -0.31
1.81 1.12 0.00
0.05 0.07 0.07
0.00 0.05 0.08 0.08
OEcOO 2E-06 6E-06 6E-06
SIMPSON'S RULE: Measurement lndex k 0 1 2 3 4
Measure- cos[w't(k)] Individual Running sin[w't(k)]/w Difference ment p(k) Contribution Integral Squared lime t(k) 0.00 0.05 0.10 0.15 0.20
1.oo 0.81 0.31 -0.31 -0.81
4.55
0.08
-1.74
0.05
0.00 0.05 0.08 0.08 0.05
OE+OO
5E-09 2E-09
Spreadsheet Learning Objectives This exercise demonstrates the following material: 'the use of columns ta store intermediate results the use of blank cells when results are unavailable or inappropriate the-use of combination cell references that are absolute for the columns but relative for the rows the use of summation, count, and average functions
the specification of a range of cells the duplication of blacks of cells far use in other parts of the s~readsheet the placement of summary values in a header t h e effect of sampling frequency an the results The significant portion of a n example assignment is displayed in Assignment 5.
Volume 69 Number 6 June 1992
493
