Excellaneous

an ad-free, spyware-free web site for
Excel users in the physical sciences

This website is meant for scientists and engineers who want to use the ubiquity, convenience and power of Excel, including its flexibility to go beyond the functionality already provided by Microsoft. It contains freely downloadable, open-access add-in functions and macros for Excel associated with my book, Advanced Excel for scientific data analysis, 2nd ed. Oxford University Press 2008. You are welcome to browse. However, just click here if all you want is the free downloads. If you first want to see what's in them, click on their contents listings, starting with the contents of the Macrobundle. The downloadable macros and functions are all in open-access VBA source code, and can therefore be modified to suit your individual needs. Most of them are self-documented (mine) or come with extensive documentation files (Volpi's). In my book you will find many of them discussed and illustrated, but you do not have to buy my book to use them. What, then, is in that book, so that you might still want to read it?

If I give you a hammer, a screwdriver, a level, a saw, a planer, a chisel, and a few more such tools, will that make you a fine carpenter? Most likely you will still need to learn how and when to use what tool. You can either apprentice yourself to a craftsman, or work through (and especially practice with) a good do-it-yourself book. That is what my Advanced Excel attemps to be.

Here is what you can expect in it: descriptions and illustrations, the latter often taken from the scientific literature, of how to select and use a particular method, how and why it works, what its limitations are, and what alternatives are available. The book is really about how to approach and solve scientific data analysis problems, and Excel is used as a widely available, readily learned, powerful and flexible tool to demonstrate and implement this. In doing so it makes you aware of its strengths as well as its weaknesses, extends the traditional boundaries of Excel, and empowers you to do more with it. Please look elsewhere for fill-in-the-blanks templates, screenshots of Excel dialog boxes, or a rehash of the Excel instruction set. And don't take my word for it, but judge for yourself, by looking at the few attached SampleSections.

But first the compulsory disclaimer. While I try to make sure that the information provided in this website is correct, and that the downloadable add-in macros and functions work properly, the contents of this website are provided as my opinion, and the downloads are offered “as is”, without any warranty. Users of information and/or downloadable material on this website implicitly agree to do so at their own risk and responsibility.

For questions, comments, and suggestions please contact me at
rdelevie@bowdoin.edu

If you want to return to my regular, academic web page, click on
www.bowdoin.edu/~rdelevie/.

Click here to advance to the Downloads.

Contents of this website

Click on any of these, or just scroll down to them.

Changes in the second edition
New features on this website
An improved macro for the first derivative
The color index
Excel & VBA pitfalls
Increasing macro precision
Some Excel books in science and engineering
The contents of the MacroBundle
The contents of the other downloads
The GNU General Public License
The downloads

Changes in the second edition

The second (2008) edition of Advanced Excel for Scientific Data Analysis involves a substantial expansion: the eight text chapters of the first edition occupied 422 pages, whereas the eleven text chapters of the second edition take up 647 pages, and this does not even count the three additional appendices. Much of this size increase comes from the three new chapters, one describing the numerical implementations of some often-used mathematical methods, one dealing with matrix algebra, and one on data accuracy.

The new chapter 9 describes numerical differentiation, integration, and smoothing in some detail, with several new macros in the MacroBundle to implement them. This chapter also introduces the concept of pE, a quantitative measure of what is usually more loosely defined as the number of significant figures.

The new chapter 10 deals with matrix algebra, and especially illustrates how to use Leonardo Volpi's impressive set of matrix instructions, which can now be downloaded also from this website. Combining the rectangular spreadsheet grid with Volpi's wizardry and a good text on linear algebra (such as Meyer's Matrix Analysis and Applied Linear Algebra) can go a long way to make this topic easily accessible.

Finally, the new chapter 11 on spreadsheet reliability discusses some common sources of error, and then illustrates Volpi's Xnumbers, also downloadable here, which will allow you to go far beyond Excel's double precision when this is necessary.

The three new appendices cover Matrix.xla, the MacroBundles and MacroMorsels, and transitioning to Excel 2007.

In order to make space for all this newly added material while keeping down the heft and price of the book, it no longer carries a print-out of the macros in the MacroBundle. In the meantime, these macros have become more numerous and more sophisticated. They are, of course, still freely downloadable from this website. And since they are in the form of Word .doc text files, you can easily read them.

Other notable changes in the second edition are the inclusion of Simonluca Santoro's contour-plotting macro in chapter 1, a discussion of the imprecision of the standard deviation in chapter 2, a much expanded description of how to deal with ionic equilibria in chapter 4, and a greatly expanded treatment of Excel's many editing and debugging tools in chapter 8.

Finally, to paraphrase Forman Acton: Of course the text is error-free — but please report any you find to rdelevie@bowdoin.edu

Click here to return to the Contents or to advance to the Downloads.

New features of this website

Many topics discussed on the website of the first edition of Advanced Excel are now included in its second edition and/or in the MacroMorsels, and therefore no longer need to be displayed here. Only two items have been retained here: the notes on the color index, because black printing ink has difficulty conveying color, and the description of extended macro precision, because you might not otherwise know that it is now possible in Excel to compute with up to 200 decimals. As before, new developments that came too late to have been included in the latest printed edition have been added here.

The number of downloadable files has increased. You will find an enlarged set of macros in the MacroBundle, many of which (recognizable as version 08) are also improved through the use of color-coded outputs and/or self-documenting cell comments. The new macros include those for integration and differentiation. The collection of MacroMorsels has also grown, and readers are cordially invited to suggest further additions or, better yet, to submit them.

A new feature is a set of cFunctions that corrects known deficiencies of Excel's standard functions. It is now perfectly doable to test Excel functions by comparing them with higher precision (i.e., longer-wordlength) results from Xnumbers, using standard methods such as can be found in Abramowitz & Stegun's Handbook of Mathematical Functions, and the Atlas of Functions by Oldham, Myland & Spanier. Those cFunctions have the name of their Excel brethren with the added prefix c for correct(ed), and are good to ± 1 in 10¹⁴ over their entire range. Their open-access source code is freely downloadable, as is the corresponding background information (in the file cFunctionComments) and the Xnumbers code used to verify their accuracy (in cFunctionValidation). As always, your comments and suggestions are welcome. Readers are especially invited to contribute to this still very small but, I hope, quickly growing collection. When your cFunction is accepted and included here, you will of course be acknowledged as its author. And many users will be grateful to you, even though you may only much later (or never) hear from them.

You will also find updated files of SampleData and SampleFunctions&Macros that allow you simply to insert rather than to type in the data sets and the special functions and macros used in the exercises in my book, and a few xMacroMorsels to help with writing macros with Xnumbers.ddl.

All of my downloadable .doc files are copyrighted under the GNU General Public License to guarantee that you can freely download, use, share, and modify this software, without risk that it will be hijacked for commercial and/or promotional use.

You may want to save and/or print the downloaded files before installing the .doc files in your Excel VBEditor module. If you dislike any of them, please let me know why, and preferably suggest ways to improve them. On the other hand, if you like them, spread the word, and if you use them in a publication, please refer to their source, i.e., to this website and/or to the book.

The latest addition is the beta version of the new macro, Deriv1, for computing the first derivative, briefly described in the next section.

In addition to the above, this website now also contains freely downlodable copies of the macro Isol.zip of Simonluca Santoro, and the many functions and macros in Leonardo Volpi's Matrix.xla, BigMatrix.xla, and Xnumbers.dll.

Please note that users of Excel 2007 may experience difficulties loading Xnumbers.dll until such time as Microsoft fixes its reg files. A possible remedy is described at the end of the section describing the xMacroBundle.

Click here to return to the Contents or to advance to the Downloads.

An improved macro for the first derivative

A new macro for numerically computing the first derivative df(x)/dx of a given function f(x) at a specified value x has been written; its beta version, Deriv1, has been incorporated in the MacroBundle, and its extended-numberlength version xDeriv1 in the xMacroBundle. The theoretical basis for this improved algorithm has been submitted for publication.

The macro uses central differencing, and allows the user to select the number j of equidistant data points of the function to be sampled, where j is an odd positive number between 3 and 15. The achievable accuracy depends, of course, on the specific function f(x) and on the particular value of the argument x at which the derivative is computed, but the macro often yields answers with at least 9 (and often 10) significant figures for j = 3, 11 significant decimals for j = 5, 12 for j = 7, and 13 for j = 9 or higher. There is little advantage in going to j-values beyond 9, because cancellation noise limits the resulting accuracy; on the other hand, there is the real disadvantage of the larger footprint at higher j-values, which increases the chance of invalidating the answer by reaching across a nearby discontinuity or singularity. A companion macro for the second derivative, Deriv2, is forthcoming.

These macros have not yet been tested extensively; if proven robust, Deriv1 will replace Deriv in the MacroBundle, and its core code will also be incorporated in the Propagation and SolverAid macros. Please try out this macro, and let me know if you encounter any problems. I will especially appreciate your comments and suggestions, preferably by email at rdelevie@bowdoin.edu.

Click here to return to the Contents or to advance to the Downloads.

The ColorIndex

The Microsoft Office suite has two sets of color schemes. The more elaborate one, called RGB, lets the user select mixtures of red, green, and blue. It is used, e.g., in the Mapper macro of the MacroBundle. A simpler, more limited color scheme, based on a small number of color samples, is used by Excel in the FillColor and FontColor icons on its Formatting toolbar. Those color swatches have names that show when you hover over them with your mouse, but in order to refer to them in a macro, you need their ColorIndex, an integer. Below you will find these, following the routine used to detrmine them.

Make two adjacent columns of numbers from 1 to 14. and two each of 15 (1) 28, 29 (1) 42, and 43 (1) 56. Highlight the top cell of the first column, containing the number 1. Switch to the VBEditor with either Alt+F11 on a pc, or Opt+F11 on a Mac. (If this is the first macro, open the module by selecting Insert > Module in the Visual Basic Toolbar.) Then write the following macro:

Sub color()
Dim cellValue As Integer
cellValue = Selection.Value
ActiveCell.Interior.ColorIndex = cellValue
Selection.Offset(1, 0).Select
End Sub

Now hit the F5 key repeatedly, then switch back to the worksheet with Alt+F11 (pc) or Opt+F11 (Mac), and see what happens. Better yet, display the spreadsheet and the macro side-by-side, without overlap, so that you can switch easily between them. (On a narrow monitor you may have to reduce the size of the speadsheet to avoid overlap.) You now should have colored the first cell black, the third red, the next ones bright green, blue, yellow, pink, turquoise, etc. Now repeat this on the third, fifth, and seventh column. Then change the word Interior in the above code into Font, and repeat the above in the even-numbered columns. This is what you should get:

ColorIndex by the numbers, for both Interior and Font.

When you click on the down arrow of Excel's color icon in the Formula bar, and then hover with your pointer over the 40 color swatches shown, you will find their names. Putting the two together yields the following display of color, ColorIndex values, and Microsoft color names:

ColorIndex and corresponding Microsoft name by the sample of FillColor and FontColor.

(Your results may differ somewhat, depending on the Excel version you use.) When you compare these two figures you will notice that there are some number duplications, some extra colors, and in general a numbering system that seems to lack an organizing principle, but that is neither here nor there.

The above macro indicates how you can manipulate cell and font colors in a macro. (A function cannot do this.) All you need to do is to select an ActiveCell, and then to specify what you want, as in

ActiveCell.Interior.ColorIndex = 8

to color the whole cell turquoise, and/or

ActiveCell.Font.ColorIndex = 5

to make its text blue.

Click here to return to the Contents or to advance to the Downloads.

Excel & VBA pitfalls

Excel is the most ubiquitous numerical software around. But that does not mean that it is flawless: popularity is no guarantee for quality. Here are some of its most egregious sources of errors; their order of importance for you depends, of course, on the type of problems you try to solve with it. At any rate: forewarned is forearmed.

1. In Excel, negation comes before exponentiation while, as usual, exponentiation comes before subtraction. Of course, negation and subtraction use the same minus symbol “–”, and because it makes a difference in Excel (whereas it should not), the software must guess from the context what you might mean, and it often guesses wrong. This is one of those places where Excel, in order to make things supposedly easier, actually makes them much worse. To add to the confusion, Excel's customer scripting language VBA doesn’t do this, but always (correctly) exponentiates first. Here is a simple example.

Say that you want to use the common central differencing formula for a first derivative,

f ^I(x) = [f(x+d) – f(x–d)] / (2 d)

with x = 1, d = 0.1, and f(x) = x². If you write =(1.1^2–0.9^2)/(2*0.1) you will find the correct result (1.21 – 0.81) / 0.2 = 0.40 / 0.2 = 2, whereas =(-0.9^2+1.1^2)/(2*0.1) will net you the incorrect answer (+ 0.81 + 1.21) / 0.2 = 2.02 / 0.2 = 10.1. However, when you use either form in a function or macro, i.e., in VBA, the answer always comes out right.

So here you have it, in a more obvious form: in Excel, +1.1^2–0.9^2 is not equal to –0.9^2+1.1^2, or + a² – b² is not the same as – b² + a². And this applies not just to squares, but to all even-integer powers of a and b.

Understanding what went wrong suggests the following remedies, which indeed work:

(1) use brackets around a term and its exponent: (a²) – (b²) = – (b²) + (a²), so that you would replace =(–0.9^2+1.1^2)/(2*0.1) by =(–(0.9^2)+ (1.1^2))/(2*0.1) or  =(–(0.9^2)+ 1.1^2)/(2*0.1); or

(2) use –1* instead of –, as in a²–1*b² = –1*b² + a², so that the above expression would read =(–1*0.9^2+1.1^2)/(2*0.1).

The following two examples are only of concern to those of you who use custom functions and macros, and illustrate mismatches between the spreadsheet and the customer scripting language that comes with it, VBA. You can find more such examples in section 1.14 of Advanced Excel, but you get the idea: test before you trust.
 
2. In Excel, LOG denotes 10-based logarithm, and LN specifies the natural logarithm, its e-based counterpart. But in VBA, log means natural logarithm, and the 10-based logarithm of the number a must be computed as Log(a)/Log(10), or by referring explicitly to the Excel formula with Application.Log(a).

3. In Excel, ROUND always rounds away from zero: ROUND(0.5) produces a 1, and ROUND(–0.5) results in –1, whereas VBA rounds to the nearest even integer, so that both Round(0.5) and Round(0.5, 0) yield 0, while Round(–0.5) and Round(–0.5, 0) displays –0 or, in scientific notation, as –0.00E–01 with as many decimal places as you care to specify. (This particular result is interesting because, despite the specific inclusion of –0 in the standard IEEE 754 protocol, Excel doesn’t recognize –0, and doesn’t even let you type it in as a number, yet it generates and displays it here!) 

Click here to return to the Contents or to advance to the Downloads.

Increasing macro precision

The macros in the MacroBundle are relatively crude, and contain none of the computational refinements that you might expect from a professional programmer or applied mathematician. (Its author is neither.) They could certainly be made to be more modular, more efficient, and more accurate. But many scientists and engineers have neither the time nor the expertise to write VBA code that squeezes the last few significant digits out of their macros. This section does not directly address that problem, but offers what may often be an end-run around it.

First a bit of semantics. We will stay with the usual distinction made in the physical sciences between accuracy and precision, with accuracy indicating how closely one approximates the true answer, and precision defining the reproducibility of a given procedure with a given method. Computers are fully deterministic machines, and are therefore (except when they malfunction) precise to within the number of digits displayed in their results. In other words, repeating a computer calculation under identical conditions should yield completely reproducible answers, for whatever they are worth, and its numerical precision should therefore equal its numberlength. For example, Excel shows all of its results in industry-standard double precision, i.e., with 64 bits, corresponding to about 15 decimal places. There is, of course, no guarantee that all (or even any) of the digits in the result of such a computation are numerically accurate. That is why institutions such as NIST publish test sets to calibrate software accuracy.

The numerical accuracy of a computer program cannot exceed its numerical precision, but it can be much smaller. Still, in many applications, double precision yields perfectly adequate accuracy. But there are situations, such as when large matrices must be manipulated, in which numerical accuracy may be degraded sufficiently to cause the analysis to produce answers of dubious usefulness. In those cases it may be necessary to use more precise software. One may not even be aware of this: what fraction of the many users of Excel's Trendline would know that, in fitting their data, Trendline uses matrix algebra?

The recently released, very powerful Xnumbers.dll freeware package from Leonardo Volpi and his team in Italy has now made it both possible and relatively easy to extend the numberlength limits of Excel macros, and hence their numerical precision. With this new software addition one can obtain much more accurate results from these macros, and one can also use it to test the accuracy of macro fragments, and to identify accuracy-limiting code. This freeware, which can be downloaded from this website as well as from its originating website, http://digilander.libero.it/foxes/, is a great gift to scientific users of Excel. You will need an unzip routine on your computer. When you use Volpi's site , which contains many other worthwhile downloads, make sure you download the xnumbers dll active-x multi-precision math library Xnumbersdll.zip, not the earlier xnumbers multi-precision floating point computing for Excel, xnumb43.zip. For details about its inner workings, see the just-mentioned website. The approach is similar to that of MPFUN77 for Fortran 77, see D. H. Bailey, ACM Trans. Math. Software 19 (1993) 288-319, and its current successor, ARPREC (arbitrary precision) for Fortran 90 and C++, see http://crd.lbl.gov/~dhbailey/mpdist/.

Xnumbers allows Excel versions 2000, 2003, and XP (when running under Windows 2000, 2003, XP, or NT) to make calculations with a user-selectable numberlength of up to 200 decimal places. If no numberlength is specified, xnumbers will use its default setting of almost quadruple precision (30 decimals, double that of Excel; IEEE 754 defines qudruple precision as 33 to 36 decimals), which is usually sufficient. Xnumbers uses different approaches for VBA macros and subroutines on the one hand, and directly on the Excel spreadsheet with functions on the other. Since the MacroBundle deals primarily with macros, we will here only describe the macro aspects of xnumbers.

Xnumbers.dll makes it possible to modify existing VBA macros (or to write new ones) that use its higher precision. This requires downloading and installing the dynamic link library xnumbers.dll. Once this is done, you can work at higher precision with relatively few modifications of the VBA macro code. Moreover, your macros can keep their input and output formats, so that, to their users, they “look and feel” exactly the same as before, except for their slower execution speed and the (often) higher accuracy of the results. Practical examples can be found in the xMacroBundle. The first macro in that bundle, xLS, is color-coded to highlight the changes involved in converting an existing macro to higher numberlength.

Click here to return to the Contents or to advance to the Downloads.

Some useful Excel books in science and engineering

Below are listed a few useful, intermediate- and advanced-level Excel books specifically geared towards scientists and engineers, with short (subjective) comments. They are arranged in alphabetical order of author last names. Two recommended recent books are prefaced by an asterisk. For a much more complete listing of the many available English-language books on Excel, often annotated with comments from publishers and readers, go to Amazon.com.

E. J. Billo, Excel for chemists, a comprehensive guide, 2^nd ed. Wiley 2001. A good intermediate book, with several chemical examples, a somewhat limited use of VBA, and excellent help for Mac users.

* E. J. Billo, Excel for scientists and engineers, Wiley 2007. An intermediate book with an emphasis on custom functions, thereby complementing my own Advanced Excel, which mostly focuses on macros. As you can see from Volpi's Matrix.xla, there are many things you can do with functions, and (unlike macros) they respond immediately, without prodding.

S. C. Bloch, Excel for Engineers and Scientists, 2^nd ed. Wiley 2003. A nice presentation, but without VBA. For inexplicable reasons, two excellent chapters on Fourier analysis and its applications are not printed, but are instead hidden on the accompanying CD.

* D. M. Bourg, Excel Scientific and Engineering Cookbook, Wiley 2000. An easy read yet full of helpful tips and suggestions.

R. de Levie, How to use Excel in Analytical Chemistry and in General Scientific Data Analysis, Cambridge Univ. Press 2001. Primarily geared towards analytical chemists.

R. de Levie, Advanced Excel for Scientific Data Analysis, 2^nd ed. Oxford Univ. Press 2008. An advanced text for a general scientific audience, with special emphasis on error analysis, least squares methods, Fourier transformation, matrix algebra, and data accuracy. Assumes basic familiarity with Excel, then uses custom macros to go well beyond the tools provided by Microsoft.

B. S. Gottfried, Spreadsheet Tools for Engineers: Excel 2000 version, 2^nd ed. McGraw-Hill 2002. Nice presentation, but (like Bloch) omits VBA. A new edition has been announced for early 2009.

W. J. Orvis , Excel for scientists and engineers, 2^nd ed., Sybex 1996. The classic book in this area, with an excellent index. A must-have, although by now beginning to show its age.

Click here to return to the Contents or to advance to the Downloads.

The contents of the MacroBundle

This is a mixture of versions, where version 08 (the latest) indicates the presence of self-documenting cell comments. (Eventually all my macros will be so modified, but unfortunately I can find no more than 25 hours in my days.) Further improvements may be inserted, and new macros added, as they become available.

The macros in the MacroBundle are self-documented, and can be downloaded freely and used without any obligation, as long as they are not used commercially. If you dislike them, please let me know why, and preferably suggest ways to improve them. On the other hand, if you like them, spread the word, and if you use them in a publication, please refer to their source, i.e., either to this web site or to the book. The more people know about the existence of these free macros, the more people may benefit from them, which is the purpose of making them freely available. Disclaimer: the macros in the MacroBundle were created to accompany my two books, How to use Excel in Analytical Chemistry and in General Scientific Data Analysis, Cambridge University Press 2001, and its recent, more general successor, Advanced Excel for Scientific Data Analysis, Oxford University Press 2004, 2008. They were written primarily to illustrate how to analyze data, and also how to write (relatively simple) macros to achieve that goal when not already available in Excel. These rather unsophisticated macros will usually do what they are supposed to do, but because of their simple input and output formats they are neither made nor meant to be foolproof, and they may not always generate the best possible results either. They are provided free of charge, and without any warranty, implied or otherwise. The same applies to the macros in the xMacroBundle, the MacroMorsels, and the xMacroMorsels. Installation instructions are included in the package, and apply to the individual macros as well as to the total bundle. In order to keep them free of commercial exploitation, they are all copyrighted under the GNU General Public License, details of which you can find in the GNU General Public License download.

The direct uses of the macros in the MacroBundle are briefly described below. They can also be used to model your own macros, be modified to suit your individual needs or tastes, or even be scavenged for useful parts.

Propagation of imprecision

Propagation computes the propagation of imprecision for a single function, for various independent input parameters with known standard deviations, or for mutually dependent parameters with a known covariance matrix.

Linear least squares

LS is a traditional least squares fitting routine for linear, polynomial, and multivariate fitting, assuming one dependent variable. LS0 forces the fit through the origin, LS1 does not. The output provides the parameter values, their standard deviations, the standard deviation of the fit to the function, the covariance matrix and, optionally, the matrix of linear correlation coefficients.
ELS provides least squares smoothing and differentiation for an equidistant (in the independent variable) but otherwise arbitrary function using a "Savitzky-Golay" moving polynomial fit. ELSfixed uses a user-selected, fixed-order polynomial, ELSauto self-optimizes the order of the fitting polynomial as it crawls along the function. Philip Barak of the University of Wisconsin contributed this macro.
WLS is the equivalent of LS with the inclusion of user-assigned weights.
GradeBySf uses the standard deviation of the fit to a multivariate function of up to six variables as an aid to find the optimum number of such variables.
LSPoly applies LS to fitting data to a polynomial of gradually increasing order (up to 14), including criteria (such as s_f and the F-test) useful for deciding how many terms to include in an analysis.
LSMulti applies LS to an increasing number of terms of a multivariate least squares analysis.
LSPermute computes the standard deviation of the fit for all possible permutations of multivariate parameters of up to six terms.

Nonlinear least squares

SolverAid provides uncertainty estimates (standard deviations, the covariance matrix, and optionally the matrix of linear correlation coefficients) for Solver-derived parameter values.
SolverScan lets Solver scan a two-dimensional array of parameter values. Requires that Solver.xla is installed.
ColumnSolver applies Solver line-by-line to column-organized data. Requires that Solver.xla is installed.

Transforms

FT is a general-purpose Fourier transform macro for forward or inverse Fourier transformation of 2ⁿ data where n is an integer larger than 2.
Gabor provides time-frequency analysis.
Ortho yields Gram-Schmidt orthogonalization.

(De)convolution

(De)convolve provides convolution and deconvolution. The convolution macro is quite generally applicable, the deconvolution macro is not.
(De)ConvolveFT yields convolution and deconvolution based on Fourier transformation.
DeconvolveIt performs iterative (van Cittert) deconvolution. DeconvolveIt0 has no constraints, DeconvolveIt1 assumes that the function is everywhere non-negative.

Calculus

Deriv uses central differencing to find the first derivative of a function.
Deriv1 is an improved version that (after further beta-testing) will replace Deriv.
DerivScan applies Deriv to a range of step sizes.
Romberg efficiently integrates a function.
Trapez uses straightforward trapezoidal integration, useful for repetitive functions.
Semi-integrate & semi-differentiate comprises two small macros for cyclic-voltammetric (de)convolution assuming planar diffusion.

Miscellany

Mapper generates
RootFinder finds a single real root by bisection.
MovieDemos has the code for the simple examples given in my Advanced Excel book.
InsertMBToolbar provides easy access to the macros of the MacroBundle.
RemoveMBToolbar

Click here to return to the Contents or to advance to the Downloads.

The contents of the other downloads

SampleSections

The sample sections contain at least one brief selection from each of the eleven chapters and three new appendices of the second edition of my Advanced Excel for Scientific Data Analysis. Even out of their chapter context, they will serve here to give you an idea of what to find in my book. Below are the sections shown in those SampleSections, together with their respective chapter headings. Asterisks * identify sections new to the second edition.

Chapter 1: Survey of Excel

* Contour maps

Chapter 2: Simple linear least squares

* How precise is the standard deviation?
* Phantom relations

Chapter 3: Further linear least squares

* Spectral mixture analysis

* The power of simple statistics

* Titration of an acid salt with a strong base

Chapter 5: Fourier transformation

* Data compression

* Iterative deconvolution using Solver
* Time-frequency analysis

* Stability
* Chaos

* Reading the contents of a cell
* Ranges & arrays
* Attaching cell comments

Chapter 9: Some mathematical operations

* Romberg trapezoidal integration

Chapter 10: Matrix operations

* Singular value decomposition

Chapter 11: Spreadsheet reliability

* The error function
* Overview of Xnumbers rules

Appendix B: Some details of Matrix.xla

* Eigenvalues and eigenvectors

Appendix C: MacroBundles & MacroMorsels

* Inherent limitations of least squares methods

Appendix D: Transitioning to Excel 2007

* A missed opportunity

The xMacroBundle

After you download and install Xnumbers.dll, you can use the macros in the xMacroBundle for high-precision computing. Since it is unlikely that you will use them routinely, they are not included in the regular MacroBundle toolbar, but a separate xMBToolbar is provided for their use. You can of course have both displayed simultaneously, but then be careful not to confuse the two.

New x-macros may appear on this site as they become available and have undergone provisional testing. So far the only macros in this collection are xLS, xWLS, xLSPoly, xOrtho, xForwardFT and xInverseFT, as well as xDeriv1 and xDeriv1Scan, which are the extended-precision equivalents of LS, WLS, LSPoly, Ortho, ForwardFT, InverseFT, Deriv1, and DerivScan respectively, plus InsertXMBToolbar and RemoveXMBToolbar.

In case you encounter problems getting Xnumbers.dll to work in Vista/Excel 2007, here is a suggestion, courtesy of Mark Parris of the Microsoft Excel team. Click on the START button, then click on Run, which will open a small Run dialog box in the bottom left corner of the screen. In its window type

REGSVR32 a Xnumbers.dll

and click on the Run button. A box should appear with the message "DIIRegisterServer in Xnumbers.dll succeeded." That should do it. Please let me know if this works for you.

Of course, if your computer is part of a network, you may get a statement that access is denied, in which case you should contact the network administrator or IT person. And if they are unable or unwilling to help you out, ask them for a Windows XP/Excel 2003 loaner.

The SampleData

The SampleData file contains the data used in the various examples in my book, Advanced Excel for Scientific Data Analysis, in order of appearance. Their page numbers refer to the current, second edition. They are provided here so that you need not type those data into the spreadsheet, a time-consuming and error-prone chore.

The SampleFunctionsAndMacros

The SampleFunctionsAndMacros file holds the short sample functions and macros used in the text of my Advanced Excel, provided here so that you need not type them in manually. Again, the page numbers refer to the latest, second edition.

The MacroMorsels

The MacroMorsels are short macros, with self-explanatory names. Each macromorsel highlights a particular aspect that may give problems to the novice macro writer. They are focused on three aspects: data input & output, data analysis, and spreadsheet & macro management. Load them just as you would the MacroBundle and the xMacroBundle; no toolbar is provided. The following MacroMorsels are currently included:

Data input & output

* ReadActiveCell
* ReadActiveArray
* InputBoxForNumber
* InputBoxForCell
* InputBoxForRange
* InputBoxForArray
* OutputASingleValue1
* OutputASingleValue2
* OutputSeveralValues
* OutputAnArray1
* OutputAnArray2
* OutputAnArray3
* ControlOutputFormat
* FromArrayValuesToRangeValues
* PreventCellOverwrite
* PreventColumnOverwrite

* Rounding
* ImportingData
* Truncation
* UseAnExcelFunction1
* UseAnExcelFunction2
* UseAnExcelFunction3
* UseAnExcelFunction4
* UseANestedExcelFunction
* UseAnExcelMatrixOperation1
* UseAnExcelMatrixOperation2
* UseAnExcelMatrixOperation3
* UseAnExcelMatrixOperation4
* InvasiveComputing
* CentralDifferencing1
* CentralDifferencing2
* DecimalOperations
* CountingPennies

* ScanSymbolCode
* UseSymbolCode
* ControlANumericalDisplay
* DeconstructACellAddress
* DeconstructAnArrayAddress
* ReconstituteAnEquation1
* ReconstituteAnEquation2
* ReconstituteAnEquation3
* MoveAndResizeARange
* InhibitScreenUpdating
* KeepUserInformed
* AttachCellComments
* MakeTestToolbar
* DeleteToolbar
* ErrorPrevention
* ErrorTrapping1
* ErrorTrapping2

The xMacroMorsels

For users of Xnumbers.ddl, this new category has been added to serve a similar function as the MacroMorsels. Don't try them out without first having installed Xnumbers.ddl! They form a separate download item because, if included with the regular MacroMorsels, they would invoke endless "compile" errors if Xnumbers.ddl were not installed. So far this collection contains only a few items:

* ExtractFullPrecisionFromSpreadsheet
* DisplayFullPrecisionResultsOnSpreadsheet
* ReconstituteAnEquationInXnumbers1
* ReconstituteAnEquationInXnumbers2

The downloads

last updated: March 23, 2009