The numerical analysis of a NIST reference data set on a spreadsheet is illustrated in detail. We describe several interesting features, such as an efficient way of centering when fitting data to a high-order polynomial, and a little-known aspect of the imprecision of the imprecision. We also discuss a powerful alternative to standard matrix inversion, an extended set of add-in matrix functions for Excel, and software for making spreadsheet calculations with extended number length.
REFERENCES
1.
A.
de Moivre
, The Doctrine of Chances
(Woodfall
, London, 1756
). Reprinted (Chelsea, New York, 1967
).2.
A. M.
Legendre
, “Nouvelle méthodes pour la détermination des orbits des comètes
” (Courcier, Paris, 1805
).3.
C. F.
Gauss
, “Theoria motus corporum coelestium in sectionibus conicis Solem ambientum
” (Perthes & Besser, Hamburg, 1809
);C. F.
Gauss
,“Theoria combinationis observationum eroribus minimus obnoxiae
” (1823, 1828
);C. F.
Gauss
,Theory of the Combination of Observations Least Subject to Errors
, translated by G. W. Stewart (SIAM
, Philadelphia, 1995
).4.
This NIST Statistical Reference Dataset can be downloaded from www.itl. nist.gov/div898/strd/lls/data/LINKS/DATA/Filip.dat.
5.
R.
de Levie
, Advanced Excel for Scientific Data Analysis
(Oxford University Press
, New York, 2004
).6.
C. D.
Meyer
, Matrix Analysis and Applied Linear Algebra
(SIAM
, Philadelphia, 2000
).7.
A.
Cayley
, “A memoir on the theory of matrices
,” Philos. Trans. R. Soc. London
148
, 17
–37
(1858
).8.
A. S.
Householder
, The Theory of Matrices in Numerical Analysis
(Blaisdell, New York, 1964
), p. 141
.9.
W. H.
Press
, B. F.
Flannery
, S. A.
Teukolsky
, and W. T.
Vetterling
, Numerical Recipes, The Art of Scientific Computing
(Cambridge University Press
, Cambridge, 1986
), especially Chaps. 2 and 14.10.
G. H.
Golub
and C.
Reinsch
, “Singular value decomposition and least squares solutions
,” Numer. Math.
14
, 403
–420
(1970
).11.
G. W.
Stewart
, Matrix Algorithms I: Basic Decompositions
(SIAM
, Philadelphia, 1998
), p. 71
.12.
Several software packages can be freely downloaded from Leonardo Volpi’s website: digilander.libero.it/foxes. We have used Matrix.xla, which includes Excel add-in functions for the singular value decomposition, and xnumberdll.zip, which allows the number length of Excel custom subroutines and macros to be enhanced.
13.
The macros of the MacroBundle can be freely downloaded from www.bowdoin.edu/~rdelevie/excellaneous. This website also contains several macros already modified to be used directly with Xnumbers, including xLS, and a tutorial on how to make such modifications in one’s own Excel macros.
14.
B. T.
Smith
, J. M.
Boyle
, J. J.
Dongarra
, B. S.
Garbow
, Y.
Ikebe
, V. C.
Klema
, and C. B.
Moler
, Matrix Eigensystem Routines–Eispack Guide
(Springer
, Berlin, 1990
), 2nd ed.15.
E.
Anderson
, Z.
Bai
, C.
Bischof
, J.
Demmel
, J. J.
Dongarra
, J.
Du Croz
, A.
Greenbaum
, S.
Hammarling
, A.
McKenney
, S.
Ostrouchov
, and D. C.
Sorensen
, LaPack User Guide
(SIAM
, Philadelphia, 1995
), 2nd ed.16.
See, for example,
P. R.
Bevington
, Data Reduction and Error Analysis for the Physical Sciences
(McGraw-Hill
, New York, 1969
), pp. 200
–203
.17.
N. R.
Draper
and H.
Smith
, Applied Regression Analysis
(Wiley
, New York, 1998
), 3rd ed.18.
D. H.
Bailey
, “A multiple precision floating point computation package
,” ACM Trans. Math. Softw.
19
, 288
–319
(1993
), extensively describes ARPREC’s FORTRAN 77 predecessor, MPFUN. For ARPREC, see crd.lbl.gov/~dhbailey/mpdist/.19.
See EPAPS Document No. E-AJPIAS-75-003706 for copies of macros and an Xnumbers tutorial. This document can be reached via a direct link in the online article’s HTML reference section or via the EPAPS homepage (http://www.aip.org/pubservs/epaps.html).
20.
J. R.
Taylor
, An Introduction to Error Analysis
(University Science Books
, Sausalito, 1996
), 2nd ed., pp. 297
–298
.21.
For an equivalent approach see
D. A.
Heiser
, “Polynomial regression
,” www.daheiser.info/excel/notes/noteo.pdf.22.
© 2007 American Association of Physics Teachers.
2007
American Association of Physics Teachers
AAPT members receive access to the American Journal of Physics and The Physics Teacher as a member benefit. To learn more about this member benefit and becoming an AAPT member, visit the Joining AAPT page.
