- Email: [email protected]
The use of spreadsheets in teaching undergraduate mathematics
Compur Educ Vol. I?, No. 1. pp. 535-538. Pnnled cn Great Bntain
1988
0360-1315038 53.00 + 0.00 Pergamon Press plc
THE USE OF SPREADSHEETS IN TEACHING UNDERGRADUATE MATHEMATICS Department
of Mathematics,
JOHN C. TURNER U.S. Naval Academy, Annapolis. MD 21402, U.S.A. (Received 2 January 1988)
Abstract-The use of spreadsheets in teaching undergraduate mathematics is discussed, with examples of specific topics. The range of application includes Calculus, Differential Equations, Probability and Numerical Analysis. The advantages and disadvantages of spreadsheets are discussed.
INTRODUCTION
Spreadsheets have had a considerable impact on computing, especially microcomputing, since their introduction. One can only speculate about the success of Apple were it not for VisiCalc, which for years was the only spreadsheet available and only for the Apple. Similarly Lotus 123 had a major impact on the acceptance of the IBM-PC in the business world. Spreadsheets have several unique characteristics. One is the so-called “point mode”, where a formula is entered by pointing at its components. Formulas are generally parsed on input, meaning that syntax errors are detected immediately. A large number of facilities are built in, most notably graphics. An X-Y plot can be obtained by pointing at the range of x and y values and pointing at the fact that it is an X-Y graph (not a pie chart). Scaling, colors, point symbols and line type is chosen automatically. (These can usually be modified, but the main point is that the results are fast and painless.) The most notable characteristic of spreadsheets is the concept of the interrelation of cells. Whenever the value in a cell changes, all cells that depend on this cell are recalculated. This proceeds automatically and immediately. In business circles, this is used to do “what if” analyses, where one or more values are varied and the impact on the results is observed. These same characteristics can be applied most fruitfully in the teaching of undergraduate mathematics courses. The principal benefits arise from the immediacy of response, the ease of built-in graphics and the feedback of the propagation of changes in values. SUPERCALC
At the U.S. of Lotus 123, features, with be applicable
4
Naval Academy, the standard spreadsheet program is SuperCalc 4. Since the success a wide variety of spreadsheet programs have arisen. These generally share the same slight variations here and there. The discussion here will deal with SC4, but should to most other spreadsheets, including Lotus 123. DIFFERENTIAL
EQUATIONS
My personal experience in using SC4 in the classroom derives from teaching differential equations. I found that spreadsheets were not only useful for the obvious topics, but for others that were thought to be so difficult that they required programming in Basic or Pascal. Spreadsheets were less threatening to those students that were less fond of the computer. Spreadsheets encouraged the more adventuresome students to explore, with some surprising results. Mostly, spreadsheets made it so easy to use the computer, that they called into question what we should be teaching our students about computing. On this last topic, I should mention that I spent one class period teaching the students to use SC4. I had been told that they were using SC4 in Physics and thought that one class period was enough to be sure we were aIt on the same footing. I discovered much later that most of the students
s]uau&Je s]!ql!m ‘~7 JOJ paid03 aq 1~33IYJOJ NUnlO aql UaqL ']q JOJ a3uaJaJaJaA!lElaJe pUe X pue ip ‘J JOJ pasn aq plnoqs sa3uaJaJaJalnlosqr!‘aJaH *z/17+ x pulez/tp+ I JoJ suunqos qlJy pue qlJnoj aql asn 01 s!73~1 aqJ .urunlo3pJ!qlaql u! aq II!MJ*I~= lyos‘x pucJ JoJ suumlo3apnpu! ,@Ea.qr?61qeqoJd ll!M 1aaqspeaJdsaqL 'd JOJ SlUaLUn%JEaqlU!ElUO3 Il!MZlSJy aql pUEJ aq II!MlSEl aqL w.Iwn~o3 E auyap uaqJ_ 'JalI?l 11 I(JOl\ ut?3aM OS I[a3aleJedas e II!JP daaq ‘aJOJaqsv ‘1~~0 JOpq e pue sluarun8Je OMI qi!~ pa1enleAa.g aAloAu! q3ca s,y aql ~03 sepu.uJojaql lt?qlaAJasq0
9/bY + CYZ+ ZYZ + 17) = v (EY+“‘JP+
J)dJP=PY
(Z/Z? + X ‘Z/JP + J)J
JP = EY
(Z/l Y + X ‘Z/JP + Jk7 JP = ZY (~‘J)JJP=
I?’
aJI?SI?~nUJJO~ aql ‘Ma!AaJ JOA 'lp put? 7 SnO!AaJd aq) JO UO!l3EJj f!Lq 12~~0 q3ea &Ipur! X le palenleAa3 uroq palsln3p33s! s,y aql~o q3eg 'xp aA$3 01 pa8eJaAc aJB asaqL 'py ‘~7 ‘zy ‘1~ ‘sa!l!luanbp JO uogc1n31w ~J!M
ssa33ns 01 /CaqaqL 'a5uo paJalua aq /cluepaaud
aql s! poqlatu x-a aql JO s!soq aqL ~o~lez~ue8~o InJaJe3s! sllnx-a%Inx
lvql lno su~1-11 l!‘pueururo3 Ado3 aql ql!m
‘JaAaMoH *l!sasn leql ila3q3ca u! uallgm /CIlgdxaaq lsnur11 'l!la8Jojpua asuo()d alpM 10~~23 nol( ‘(X‘I)d= Jp/xp JI 3laaqspeaJds u! suo!lsunJ pauyap-Jasn ou aJe aJay leql s! paJaluno3ua walqoJd ISJLJaq_L -dEala%JI?l .@U!SfJdJnS f!s! poqlam Elln)I-a%In~ aql~oJ SlaayspeaJds%u!Sfi
*uoy\?Jo~dxa JO lahal awes aql al!Au!01 waas lou op sLueJ%oJd 3!seflwaql ql!M Jalu!l 01 siuapnls sa%eJno3ua wql slaaqspk?aJds lnoqr!%J!qlawos aq 01 suxaasosle aJaqL 3luapnls kwu ayw
J~J Jnoq UB 01 U!UI~C woq
p[noM q3!qM 'UEJ8OJd 3!seg %U!pUOdSaJJO3 aql 01 palseJluo3 s! s!qL *salnu!uIu! paJnseaw
s! poqlam s,JalngJo 1InsaJaql ~0 qdl?J8 ls~y aql a3npOJd 01 paJ!nbaJ aug
aql lEq1 aAJasq0 ('slla3 UMO
J!aqlu! Ip ayq sJalaLut?JL?d %u!daay se q3ns ‘SlaaqSpEaJdSu! aILls%u!uIuJeJ%oJd ~00% s! aJaq1 leql paJaAoZIs!p 1 ‘/Cl[eluap!3uI) .anleAsl!Jallt! 'JaqlJnd 'Da&a aqlaAJasq0 pw
0) kSI?as! l!'IlaDalm?das ?? u! lday s! JPJO anp?A aql J!
uo!l!puo3 p3!l!u! aql Jalle01 Jaw3.u aldw!s a MOU s!11 .paDnpoJd s!
qdvJ% e pUE SayOJlSLaq aJOUI MaJ V 'Ip/xp JOJ e[nu.IJoj aqlu! ad4 paJ!nbaJ aILI!1 aql A[Jeau LJaA s! iaaqspealdsaJ!luaaql JOJ aLug aq_L .laaqspeaJdsaql saqs!uy a%wJ 01 MOJJO Ado3 t!snqL (.x JOJ anleA le!l!u! aql su!eluo3 MOJ IsJy aqL)'puo3as aql se awes aql aJe SMOJ %u!paawns 11~‘MON *M~J
puo3as aql 01@3aJJo3 pa!doa aq ~I!MLaql uaql ‘sclnuuoJ aqi u! pasn aJe sa3uaJaJaJIla3aA!lelaJJ~ ./cpuEqS! SlaaqSpEaJdSJo uog3unj ICdo3 aql aJaqM s! s!qJ *ls~yaql*~oJSE aures aql uaql aJe MOJ
ixau aqi JOJ suoyejn3le3 aql 'MOJ lxau aqi u! ‘x 10~ anleA lxau aql spla$ s!qL *tp*/is! xp leql lsa88ns 01 ha al!nb s!l!‘,, Jp/Xp = /i ,, pue ,,X,, pa\\aqEluWnl03 E ql!M epoqlaw aql @3npoJlu! lnoql!m auop aq uaha UKJ pue SlaaqspeaJds u! luauxaldm! 01 hea al!nb s! poqlam @wJOJ
s‘Ja1ng .spoqlaLuellnx-a8una
pue Jalng aprq3u! ga
Vlll-lX-39Nflll
QNV
u! SlaaqspEaJds JOJ sD!dol sno!Aqo aqL
ClOHiLL3h’ S.X31f-l3
.ayws!w e aperu peq Jalndruo3 aql lq%noql Layi fuxaql01 ldawoj Mau Xlalaldruo3a SBM s!qL .saJn%yluesy!u%!sIlesasol snql pur?sanp2A a%el 6JaA si3eJiqnspue sppe LlaleuJalle poqiaur sa!Jas aql 'OE puoLaq x Jo3 leql s! uosEaJ aql *paye Laql ‘,,$qM,, 3q1 puoAaq ,,zznJ,, OlU! paAloss!p uaql put? of 01 in0 h[a3!UpaAeqaq sqdaJ8 J!aql leql paAJasqo uaql LaqL '0s 01 lno a%ueJ 01 x paMo]le pue X uo l!rn!l p?!3y!lJE ut?sBM S!qlleql pazgeaJ waqlJ0 awes '01 > x> 0 JOJ sapas Lq l!aleln3le301 aJaM LaqI .(X)Ofuo!vunJ lassaH aql %u!lc1n3p3 aJaM sluapnls h,u ‘aleJlsnll! 0L .papnlDu!aq lsnw gopunoJ ayq s2!dol leD!JauInuaJotu ‘8u!ql au0 Jod ipapnlw! aq ~I!Masla3eqM ‘%u!uJLueJ%oJd1aaqspeaJds JOJ q%noua s! %u!laam ssel3au0 J! ina %I!LuuIt?J8oJdsap!saq asJno3 JalndtuoD hol3npoJlu! ue o)u! 0% ss!dol Jaqlo '/CIu!t?lJa3 wJeJ%oJd
lsaldtu!s aql al!JM 01 alqe b[aJeq sluapnls
isoulsaAeaipue sJnoq Jalsawas IeJaAasSaJ!nbaJqxqM asJno3 8ugndwos
LJol3npoJw! p3-tsnaqlol
palseJluo3aq pInoqss!qL jpa.\!aDaJ Kay] 11~SBM uog3nJlsu! Km lvql pues$qd
u! i!%u!snw~aJaM
The use of spreadsheets
in teaching
undergraduate
mathematics
537
already available to its left. These 3 columns can also be used as is for k3. They can also be used for k4, but the “/2”s must be edited out. A summary of the columns for R-K is: (1) (2) (3) (4)
t = previous t + dt; .Y= previous x + d.y; k 1 + dt *F (arguments on left); t + dt/2; (5) x + k l/2 where x is absolute column 2, but k I is 2 columns left; (6) k2, copied from column 3; (7-9) copy 4-6; (10-12) copy 4-6 and delete j2; (13) dx calculated from k 1, k2, k3, k4. This is quite a bit more complicated than Euler’s method, but is not as bad as it seems. It emphasizes the distinction between absolute and relative cell references. If different functions are to be used, only the columns for k 1, k2, k3 and k4 must be changed, and after k 1 is changed, it can be copied to the others, remembering to remove the /2 from k4.
BESSEL
FUNCTIONS
AND
FOURIER
SERIES
In DE, series appear in two different guises. Bessel functions arise as series solutions to DE. Fourier series, on the other hand, are used to represent solutions to DE. The spreadsheet techniques for both are similar. A separate column is used for each term in the series. The function is then defined to be the sum across the columns. In the case of Fourier series, many solutions only involve odd (or even) terms. In such cases, it is handy to define a row across the top of the columns that contains the index for that column. This method of representing sums where every term is kept in a cell is quite expensive in terms of storage. In the case of the Fourier series for the step function, it is not normally possible to resolve the Gibbs phenomenon while still showing the entire range of the function with enough terms to be quite flat. Sometimes the recalculation time becomes excessive, even on quite fast computers. However, there are benefits to this approach. In the case of the Bessel function, the student can see which terms are preventing convergence by looking across the row. This clearly shows the alternative nature of the series, while also demonstrating that the high order terms converge to zero. This is more remarkable when the same comparison is made for larger values of x. The same pattern is noted, but it is clear that you must go to much higher terms to obtain convergence. The Fourier Series is most interesting when combined with exponential damping to become the solution to the heat (diffusion) equation. If the value of t is kept in a separate cell and assuming x goes down the sheet, changing the value of t shows that not only do all the terms for a given ,Ydecrease, they change in character, with some decreasing more than others. (The high frequency terms damp out more rapidly than the low frequency terms.) This gives true insight into the value of Fourier Series in the given problem. That is, Fourier Series is not just a trick to be able to write down the solution, it is a way of looking at the problem that makes certain features of the solution apparent. Two anecdotes should be related concerning Bessel functions in spreadsheets. As noted earher, some students tried to evaluate J,(x) for x near 50 and found out what numerical problems really are. As the instructor, I discovered that it was no simple matter to determine the correct value of J,,(50). Few standard tables go that far. It would be interesting to have the students verify that their programs calculated the correct values. The second story involves the fact that the students were to calculate Jc--Ja. Looking at the assignments, I was struck by one that was unlike the others. Closer inspection revealed that it was J, ?. The students realized that they only needed values of the Gamma function to do fraction orders. (Without it, they are only off by a scale factor.) Inquiring around the Department. I was unable to find another faculty member who had ever seen a graph of the fractional orders, even
538
JOHN
C. TUR~XR
though their existence is not doubted. There is truly something remarkable about spreadsheets if the students are teaching the instructors. (Neither of the students was a math major, incidentally.) OTHER
APPLICATIONS
Besides DE, spreadsheets can be useful in other mathematics courses. Most spreadsheets contain a random number function. This can be used in Probability to simulate various random variables. This can give a feel for, say, Poisson random variables or exponentials. It can also be used to demonstrate sampling distributions and the CentraI Limit Theorem. In Numerical Analysis or Numerical Methods, the bisection method is easy to program, using the IF( ) function. A row would contain 6 columns for the left end point, right end point, midpoint and function evaluations. The IF( ) function would allow the next row to use the correct endpoints from the previous row. Similarly, Newton’s Method for finding roots can be programmed. Spreadsheets would be particularly appropriate for the cases where Newton’s Method “blows up”. Calculus would provide the obvious application of the spreadsheet as a graphing program. It would also provide a tool for numerical methods such as above, if included in this course. It could also be used to demonstrate Riemann sums. SUMMARY
Spreadsheets have broad applicability in undergraduate mathematics courses. They are well accepted by the students, who learn them very quickly. Spreadsheets allow certain insights into problems that are not obvious in other methods of solution, including writing Basic programs. They greatly enhance the exploration of students into problems. At the same time, spreadsheet raise the question of what students should be taught in introductory computing courses.
1988
0360-1315038 53.00 + 0.00 Pergamon Press plc
THE USE OF SPREADSHEETS IN TEACHING UNDERGRADUATE MATHEMATICS Department
of Mathematics,
JOHN C. TURNER U.S. Naval Academy, Annapolis. MD 21402, U.S.A. (Received 2 January 1988)
Abstract-The use of spreadsheets in teaching undergraduate mathematics is discussed, with examples of specific topics. The range of application includes Calculus, Differential Equations, Probability and Numerical Analysis. The advantages and disadvantages of spreadsheets are discussed.
INTRODUCTION
Spreadsheets have had a considerable impact on computing, especially microcomputing, since their introduction. One can only speculate about the success of Apple were it not for VisiCalc, which for years was the only spreadsheet available and only for the Apple. Similarly Lotus 123 had a major impact on the acceptance of the IBM-PC in the business world. Spreadsheets have several unique characteristics. One is the so-called “point mode”, where a formula is entered by pointing at its components. Formulas are generally parsed on input, meaning that syntax errors are detected immediately. A large number of facilities are built in, most notably graphics. An X-Y plot can be obtained by pointing at the range of x and y values and pointing at the fact that it is an X-Y graph (not a pie chart). Scaling, colors, point symbols and line type is chosen automatically. (These can usually be modified, but the main point is that the results are fast and painless.) The most notable characteristic of spreadsheets is the concept of the interrelation of cells. Whenever the value in a cell changes, all cells that depend on this cell are recalculated. This proceeds automatically and immediately. In business circles, this is used to do “what if” analyses, where one or more values are varied and the impact on the results is observed. These same characteristics can be applied most fruitfully in the teaching of undergraduate mathematics courses. The principal benefits arise from the immediacy of response, the ease of built-in graphics and the feedback of the propagation of changes in values. SUPERCALC
At the U.S. of Lotus 123, features, with be applicable
4
Naval Academy, the standard spreadsheet program is SuperCalc 4. Since the success a wide variety of spreadsheet programs have arisen. These generally share the same slight variations here and there. The discussion here will deal with SC4, but should to most other spreadsheets, including Lotus 123. DIFFERENTIAL
EQUATIONS
My personal experience in using SC4 in the classroom derives from teaching differential equations. I found that spreadsheets were not only useful for the obvious topics, but for others that were thought to be so difficult that they required programming in Basic or Pascal. Spreadsheets were less threatening to those students that were less fond of the computer. Spreadsheets encouraged the more adventuresome students to explore, with some surprising results. Mostly, spreadsheets made it so easy to use the computer, that they called into question what we should be teaching our students about computing. On this last topic, I should mention that I spent one class period teaching the students to use SC4. I had been told that they were using SC4 in Physics and thought that one class period was enough to be sure we were aIt on the same footing. I discovered much later that most of the students
s]uau&Je s]!ql!m ‘~7 JOJ paid03 aq 1~33IYJOJ NUnlO aql UaqL ']q JOJ a3uaJaJaJaA!lElaJe pUe X pue ip ‘J JOJ pasn aq plnoqs sa3uaJaJaJalnlosqr!‘aJaH *z/17+ x pulez/tp+ I JoJ suunqos qlJy pue qlJnoj aql asn 01 s!73~1 aqJ .urunlo3pJ!qlaql u! aq II!MJ*I~= lyos‘x pucJ JoJ suumlo3apnpu! ,@Ea.qr?61qeqoJd ll!M 1aaqspeaJdsaqL 'd JOJ SlUaLUn%JEaqlU!ElUO3 Il!MZlSJy aql pUEJ aq II!MlSEl aqL w.Iwn~o3 E auyap uaqJ_ 'JalI?l 11 I(JOl\ ut?3aM OS I[a3aleJedas e II!JP daaq ‘aJOJaqsv ‘1~~0 JOpq e pue sluarun8Je OMI qi!~ pa1enleAa.g aAloAu! q3ca s,y aql ~03 sepu.uJojaql lt?qlaAJasq0
9/bY + CYZ+ ZYZ + 17) = v (EY+“‘JP+
J)dJP=PY
(Z/Z? + X ‘Z/JP + J)J
JP = EY
(Z/l Y + X ‘Z/JP + Jk7 JP = ZY (~‘J)JJP=
I?’
aJI?SI?~nUJJO~ aql ‘Ma!AaJ JOA 'lp put? 7 SnO!AaJd aq) JO UO!l3EJj f!Lq 12~~0 q3ea &Ipur! X le palenleAa3 uroq palsln3p33s! s,y aql~o q3eg 'xp aA$3 01 pa8eJaAc aJB asaqL 'py ‘~7 ‘zy ‘1~ ‘sa!l!luanbp JO uogc1n31w ~J!M
ssa33ns 01 /CaqaqL 'a5uo paJalua aq /cluepaaud
aql s! poqlatu x-a aql JO s!soq aqL ~o~lez~ue8~o InJaJe3s! sllnx-a%Inx
lvql lno su~1-11 l!‘pueururo3 Ado3 aql ql!m
‘JaAaMoH *l!sasn leql ila3q3ca u! uallgm /CIlgdxaaq lsnur11 'l!la8Jojpua asuo()d alpM 10~~23 nol( ‘(X‘I)d= Jp/xp JI 3laaqspeaJds u! suo!lsunJ pauyap-Jasn ou aJe aJay leql s! paJaluno3ua walqoJd ISJLJaq_L -dEala%JI?l .@U!SfJdJnS f!s! poqlam Elln)I-a%In~ aql~oJ SlaayspeaJds%u!Sfi
*uoy\?Jo~dxa JO lahal awes aql al!Au!01 waas lou op sLueJ%oJd 3!seflwaql ql!M Jalu!l 01 siuapnls sa%eJno3ua wql slaaqspk?aJds lnoqr!%J!qlawos aq 01 suxaasosle aJaqL 3luapnls kwu ayw
J~J Jnoq UB 01 U!UI~C woq
p[noM q3!qM 'UEJ8OJd 3!seg %U!pUOdSaJJO3 aql 01 palseJluo3 s! s!qL *salnu!uIu! paJnseaw
s! poqlam s,JalngJo 1InsaJaql ~0 qdl?J8 ls~y aql a3npOJd 01 paJ!nbaJ aug
aql lEq1 aAJasq0 ('slla3 UMO
J!aqlu! Ip ayq sJalaLut?JL?d %u!daay se q3ns ‘SlaaqSpEaJdSu! aILls%u!uIuJeJ%oJd ~00% s! aJaq1 leql paJaAoZIs!p 1 ‘/Cl[eluap!3uI) .anleAsl!Jallt! 'JaqlJnd 'Da&a aqlaAJasq0 pw
0) kSI?as! l!'IlaDalm?das ?? u! lday s! JPJO anp?A aql J!
uo!l!puo3 p3!l!u! aql Jalle01 Jaw3.u aldw!s a MOU s!11 .paDnpoJd s!
qdvJ% e pUE SayOJlSLaq aJOUI MaJ V 'Ip/xp JOJ e[nu.IJoj aqlu! ad4 paJ!nbaJ aILI!1 aql A[Jeau LJaA s! iaaqspealdsaJ!luaaql JOJ aLug aq_L .laaqspeaJdsaql saqs!uy a%wJ 01 MOJJO Ado3 t!snqL (.x JOJ anleA le!l!u! aql su!eluo3 MOJ IsJy aqL)'puo3as aql se awes aql aJe SMOJ %u!paawns 11~‘MON *M~J
puo3as aql 01@3aJJo3 pa!doa aq ~I!MLaql uaql ‘sclnuuoJ aqi u! pasn aJe sa3uaJaJaJIla3aA!lelaJJ~ ./cpuEqS! SlaaqSpEaJdSJo uog3unj ICdo3 aql aJaqM s! s!qJ *ls~yaql*~oJSE aures aql uaql aJe MOJ
ixau aqi JOJ suoyejn3le3 aql 'MOJ lxau aqi u! ‘x 10~ anleA lxau aql spla$ s!qL *tp*/is! xp leql lsa88ns 01 ha al!nb s!l!‘,, Jp/Xp = /i ,, pue ,,X,, pa\\aqEluWnl03 E ql!M epoqlaw aql @3npoJlu! lnoql!m auop aq uaha UKJ pue SlaaqspeaJds u! luauxaldm! 01 hea al!nb s! poqlam @wJOJ
s‘Ja1ng .spoqlaLuellnx-a8una
pue Jalng aprq3u! ga
Vlll-lX-39Nflll
QNV
u! SlaaqspEaJds JOJ sD!dol sno!Aqo aqL
ClOHiLL3h’ S.X31f-l3
.ayws!w e aperu peq Jalndruo3 aql lq%noql Layi fuxaql01 ldawoj Mau Xlalaldruo3a SBM s!qL .saJn%yluesy!u%!sIlesasol snql pur?sanp2A a%el 6JaA si3eJiqnspue sppe LlaleuJalle poqiaur sa!Jas aql 'OE puoLaq x Jo3 leql s! uosEaJ aql *paye Laql ‘,,$qM,, 3q1 puoAaq ,,zznJ,, OlU! paAloss!p uaql put? of 01 in0 h[a3!UpaAeqaq sqdaJ8 J!aql leql paAJasqo uaql LaqL '0s 01 lno a%ueJ 01 x paMo]le pue X uo l!rn!l p?!3y!lJE ut?sBM S!qlleql pazgeaJ waqlJ0 awes '01 > x> 0 JOJ sapas Lq l!aleln3le301 aJaM LaqI .(X)Ofuo!vunJ lassaH aql %u!lc1n3p3 aJaM sluapnls h,u ‘aleJlsnll! 0L .papnlDu!aq lsnw gopunoJ ayq s2!dol leD!JauInuaJotu ‘8u!ql au0 Jod ipapnlw! aq ~I!Masla3eqM ‘%u!uJLueJ%oJd1aaqspeaJds JOJ q%noua s! %u!laam ssel3au0 J! ina %I!LuuIt?J8oJdsap!saq asJno3 JalndtuoD hol3npoJlu! ue o)u! 0% ss!dol Jaqlo '/CIu!t?lJa3 wJeJ%oJd
lsaldtu!s aql al!JM 01 alqe b[aJeq sluapnls
isoulsaAeaipue sJnoq Jalsawas IeJaAasSaJ!nbaJqxqM asJno3 8ugndwos
LJol3npoJw! p3-tsnaqlol
palseJluo3aq pInoqss!qL jpa.\!aDaJ Kay] 11~SBM uog3nJlsu! Km lvql pues$qd
u! i!%u!snw~aJaM
The use of spreadsheets
in teaching
undergraduate
mathematics
537
already available to its left. These 3 columns can also be used as is for k3. They can also be used for k4, but the “/2”s must be edited out. A summary of the columns for R-K is: (1) (2) (3) (4)
t = previous t + dt; .Y= previous x + d.y; k 1 + dt *F (arguments on left); t + dt/2; (5) x + k l/2 where x is absolute column 2, but k I is 2 columns left; (6) k2, copied from column 3; (7-9) copy 4-6; (10-12) copy 4-6 and delete j2; (13) dx calculated from k 1, k2, k3, k4. This is quite a bit more complicated than Euler’s method, but is not as bad as it seems. It emphasizes the distinction between absolute and relative cell references. If different functions are to be used, only the columns for k 1, k2, k3 and k4 must be changed, and after k 1 is changed, it can be copied to the others, remembering to remove the /2 from k4.
BESSEL
FUNCTIONS
AND
FOURIER
SERIES
In DE, series appear in two different guises. Bessel functions arise as series solutions to DE. Fourier series, on the other hand, are used to represent solutions to DE. The spreadsheet techniques for both are similar. A separate column is used for each term in the series. The function is then defined to be the sum across the columns. In the case of Fourier series, many solutions only involve odd (or even) terms. In such cases, it is handy to define a row across the top of the columns that contains the index for that column. This method of representing sums where every term is kept in a cell is quite expensive in terms of storage. In the case of the Fourier series for the step function, it is not normally possible to resolve the Gibbs phenomenon while still showing the entire range of the function with enough terms to be quite flat. Sometimes the recalculation time becomes excessive, even on quite fast computers. However, there are benefits to this approach. In the case of the Bessel function, the student can see which terms are preventing convergence by looking across the row. This clearly shows the alternative nature of the series, while also demonstrating that the high order terms converge to zero. This is more remarkable when the same comparison is made for larger values of x. The same pattern is noted, but it is clear that you must go to much higher terms to obtain convergence. The Fourier Series is most interesting when combined with exponential damping to become the solution to the heat (diffusion) equation. If the value of t is kept in a separate cell and assuming x goes down the sheet, changing the value of t shows that not only do all the terms for a given ,Ydecrease, they change in character, with some decreasing more than others. (The high frequency terms damp out more rapidly than the low frequency terms.) This gives true insight into the value of Fourier Series in the given problem. That is, Fourier Series is not just a trick to be able to write down the solution, it is a way of looking at the problem that makes certain features of the solution apparent. Two anecdotes should be related concerning Bessel functions in spreadsheets. As noted earher, some students tried to evaluate J,(x) for x near 50 and found out what numerical problems really are. As the instructor, I discovered that it was no simple matter to determine the correct value of J,,(50). Few standard tables go that far. It would be interesting to have the students verify that their programs calculated the correct values. The second story involves the fact that the students were to calculate Jc--Ja. Looking at the assignments, I was struck by one that was unlike the others. Closer inspection revealed that it was J, ?. The students realized that they only needed values of the Gamma function to do fraction orders. (Without it, they are only off by a scale factor.) Inquiring around the Department. I was unable to find another faculty member who had ever seen a graph of the fractional orders, even
538
JOHN
C. TUR~XR
though their existence is not doubted. There is truly something remarkable about spreadsheets if the students are teaching the instructors. (Neither of the students was a math major, incidentally.) OTHER
APPLICATIONS
Besides DE, spreadsheets can be useful in other mathematics courses. Most spreadsheets contain a random number function. This can be used in Probability to simulate various random variables. This can give a feel for, say, Poisson random variables or exponentials. It can also be used to demonstrate sampling distributions and the CentraI Limit Theorem. In Numerical Analysis or Numerical Methods, the bisection method is easy to program, using the IF( ) function. A row would contain 6 columns for the left end point, right end point, midpoint and function evaluations. The IF( ) function would allow the next row to use the correct endpoints from the previous row. Similarly, Newton’s Method for finding roots can be programmed. Spreadsheets would be particularly appropriate for the cases where Newton’s Method “blows up”. Calculus would provide the obvious application of the spreadsheet as a graphing program. It would also provide a tool for numerical methods such as above, if included in this course. It could also be used to demonstrate Riemann sums. SUMMARY
Spreadsheets have broad applicability in undergraduate mathematics courses. They are well accepted by the students, who learn them very quickly. Spreadsheets allow certain insights into problems that are not obvious in other methods of solution, including writing Basic programs. They greatly enhance the exploration of students into problems. At the same time, spreadsheet raise the question of what students should be taught in introductory computing courses.