Spreadsheet basics Making 2-D graphs Making 3-D surface graphs Making surface maps Making movies Printing, copying, linking & embedding Setting up the spreadsheet
1.7.1 1.7.2 1.7.3 1.7.4 1.7.5 1.7.6 1.7.7
Data Analysis Toolpak Solver VBA Help File Additional macros Additional files Commercial tools Choosing the default settings
1.8 1.9
1.10 1.11
Importing data Error messages Help Functions, subroutines & macros
1.11.1 1.11.2
Custom functions Custom subroutines & macros
1.12 1.13
An example: interpolation Handling the math
1.13.1 1.13.2
Complex numbers Matrices
1.14
Handling the funnies
1.14.1 1.14.2 1.15 1.16 1.17 1.18
The binomial coefficient The exponential error function complement
2
Simple linear least squares
2.1
Repeat measurements
Algorithmic accuracy Mismatches between Excel and VBA Summary For further reading
R. de Levie, Advanced Excelfor scientific data analysis 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12 2.13 2.14 2.15 2.16 2.17 2.18
Fitting data to a proportionality LinEst Regression LS Trendline Fitting data to a straight line Simple propagation of imprecision Interdependent parameters Centering Extrapolating the ideal gas law Calibration curves Standard addition The intersection of two straight lines Computing the boiling point of water Phantom relations Summary For further reading
Fitting data to a polynomial Fitting data to a parabola The iodine vapor spectrum The intersection of two parabolas Multiparameter fitting The infrared spectrum of H35C1 Spectral mixture analysis How many adjustable parameters? The standard deviation of the TheF-test Orthogonal polynomials Gas-chromatographic analysis of ethanol Raman spectrometric analysis of ethanol Heat evolution during cement hardening Least squares for equidistant data Weighted least squares An exponential decay Enzyme kinetics Fitting data to a Lorentzian Miscellany The boiling point of water The vapor pressure of water
Fitting data to a high-order polynomial Summary For further reading
4
Nonlinear least squares
4.1
Cosmic microwave background radiation The l2 potential energy vs. distance profile Titrating an acid with a strong base Conductometric titration of an acid mixture Fitting a luminescence decay Fitting a curve with multiple peaks Fitting a multi-component spectrum with wavenumber-shifted constituents Constraints Fitting a curve through fixed points Fitting lines through a common point Fitting a set of curves Fitting a discontinuous curve Piecewise fitting a continuous curve Enzyme kinetics, once more The Lorentzian revisited Linear extrapolation Guarding against false minima General least squares fit to a straight line General least squares fit to a complex quantity Miscellany
Viscosity vs. temperature and pressure Potentiometric titration of a diprotic base Analyzing light from a variable star The growth of a bacterial colony Using NIST data sets
R. de Levie, Advanced Excelfor scientific data analysis 5.8 5.9 5.10 5.11 5.12
Interpolation Data compression Analysis of the tides Summary For further reading
261 265 268 277 279
6
Convolution, deconvolution, and time-frequency analysis
Time-dependent filtering Convolution of large data sets Unfiltering Convolution by Fourier transformation Deconvolution by Fourier transformation Iterative van Cittert deconvolution Iterative deconvolution using Solver Deconvolution by parameterization Time-frequency analysis The echolocation pulse of a bat Summary For further reading
280 285 291 295 300 311 321 325 331 335 337 338
7
Numerical integration of ordinary differential equations
339
The explicit Euler method The semi-explicit Euler method Using custom functions Extreme parameter values The explicit Runge-Kutta method The Lotka oscillator 1 The Lotka oscillator 2 The Lotka oscillator 3 Stability Chaos Summary For further reading
Terms & conditions Insert a toolbar Insert a menu Movie demos Lagrange interpolation SolverScan Calling Solver with VBA Programming details Possible extensions Mapper RootFinder
Appendix A.I A.2 A.3 A.4 A. 5 A. 6 A.7 A.8 A.9
The basic spreadsheet operations Some common mathematical functions Trigonometric and related functions Some engineering functions Functions involving complex numbers Matrix operations Excel error messages Some shortcut keystrokes for pc & Mac Installation requirements & suggestions
