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, 3rd ed., Atlantic Academic 2012. You are of course 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 attached, 2-part 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.
The latest (3rd) edition of Advanced Excel
The third edition of my Advanced Excel for scientific data analysis, published by Atlantic Academic LLC, is now available from Amazon. While its structure has not changed from the second edition (which was published by Oxford University Press), its page size has (from 6" by 9¼" to 8½" by 11") so that the book could include more material without bursting at its spine. It now contains several additional visual tools, and has an even stronger focus on convenience, precision and accuracy. In the new edition you will find many new functions, with explicit examples and applications, especially for using matrix algebra and extended precision software. It contains much new material that you will not find in any other Excel book.
Here is a demo of the type of routines described in this latest edition of my book, thanks to the work of Leonardo Volpi, who started writing the add-in programs Matrix and XNumbers, and that of John Beyers, who has now taken over their further development, and contributed the XN routines used below. The illustration highlights singular value decomposition (SVD), which is the generalization to rectangular matrices of eigenvalue decomposition, whereas eigenvalue analysis applies only to square matrices. SVD provides the most robust of the available linear least squares methods, and forms the mathematical basis of many recent chemometric methods, such as principal component analysis, partial least squares, canonical correlation analysis, reduced rank regression, factor analysis, etc. Here is good old Excel, doing SVD of a complex rectangular matrix at a higher precision than you are likely to need, now or in the foreseeable future, but with the convenience, transparency, and simplicity of the spreadsheet. No special code to learn; what you see is what you get; and the Paste Function dialog box displays the instruction names and shows you their required and optional arguments, in their proper order. Merely select the appropriate function, and provide it with the necessary input information, just as with any other Excel function. The example shown here was computed on Excel 2000 with XN.xla version 6051-7A; for Excel 2007 and 2010 use XN.xlam instead.
The spreadsheet computes or recalculates (when you introduce different input data and/or change its computational precision) to 600 decimal precision in about one second on my old Dell Optiplex 745 with an Intel x86 3.4 GHz processor, and virtually instantaneously at a precision of 50 decimals. The boxes and labels were added separately, using the standard Excel tools.The spreadsheet shows a small, complex rectangular matrix C in split format, i.e., with its real and imaginary components in side-by-side blocks, in B3:C5 and D3:E5 respectively. (Two other formats, including Excel’s way to cram both real and imaginary components into a single cell, are also supported, and are selected with 2 or 3 instead of 1 as the second argument. The default is the split format shown here, which is used throughout my book.) The precision, specified in cell B7, is referred to in the third argument, where you can also hard-code it as a fixed number if you wish, or delete it if quadruple precision is all you want. Cell blocks B10:E12, B15:E16, and B19:E20 show the three components U, S, and V of the singular value decomposition of the complex rectangular matrix C as C = U S VH, where S is the diagonal matrix containing the singular values si in decreasing order of magnitude, and the superscript H on V denotes the conjugate (Hermitian, or adjoint) transpose. That the product of these three matrices will indeed reconstruct C is illustrated in cells B23:E25, which (when left-aligned) show the leading digits of the answer, which is here computed to the 600 decimals specified in cell B7. (When you use a precision of 50 or 100 decimals, good enough for almost any problem, the response would be virtually instantaneous.) The number of decimals displayed is determined by the widths of columns B through E and the font type and size used; you can of course also display them in double precision, but that defeats the purpose if these results are used subsequently in the analysis. Overflow into the empty column F was prevented by placing apostrophes (which do not show) in cells F10:F25; any other (showing) symbol, such as a period, letter(s), number(s), etc. would do as well. (A note for connoisseurs: these functions use the “compact” SVD formalism, in which U is m´n, S is n´n, and Vis n´n, when C is m´n. For the more symmetrical, “full” formalism, in which U is m´m and S is m´n, just add the requisite number of zero elements. The functions do not require that m³n; when m<n, the compact formalism yields U as m´m, S as m´n, and V as n´n.)
The difference between the values in B3:E5 and B23:E25 is shown in B28:E30, where you see that the relative computational error is of the order of 1E-599, in other words, all digits except the last are compu ted correctly! In normal use you may not want to display the data this way, but rather rounded to, e.g., double precision. That is easy enough to do with the instruction xCDbl, which Converts from extended to Double precision, but this should of course be done only with the final result of your calculation.
By left-aligning we here show the first few digits of all data points; the full display of the contents of, e.g., cell C25 would show a nine, a decimal point, followed by 598 zeroes and, at the very end, as the 600th digit, a 3, because the calculated number has an absolute error of +3E-599. A similar examination of the value in cell B24 with, e.g., the instruction =B24 in an empty spreadsheet cell, say cell B27, but be prepared for them to take up quite a few columns! In this case it will show a minus sign, a zero, a comma, 598 nines, and a 4, because the absolute error is now -6E-600. Note that these numbers are left-aligned, and therefore will not show any exponentials; by making them right-aligned, you see their tail ends.
What does this calculation take? All of the Excel instructions used are shown here, only functions are used, no macros are involved, nothing is done and/or kept hidden in background. (Even the functions themselves, unlike Excel's, are open-access, although you will need to know something about VBA to be able to “read” their code.) The commands used are of course block instructions, because that is how Excel deals with matrices, i.e., the appropriately sized blocks are highlighted before you type the command, the instruction is typed, and then entered with the block enter command CtrlÈShiftÈEnter. And, of course, you must have XN.xla or XN.xlam loaded onto your machine and incorporated in your Excel.
XN is freely downloadable software, without ads or other garbage. You select its precision, within the limit determined by the version loaded; the maximum precision available at present ranges from 630 (for the fastest version, which we typically use) up to 4030 decimals. The input numbers in C (in block B3:E5) are not restricted to integer values; these are used here only so that the answers in B23:E25 are more easily interpreted. My book describes how SVD can be used, and shows several detailed examples.
The SVD algorithms used here are not new, but thanks to John Beyers this type of advanced computer math is now available in the convenient and ubiquitous spreadsheet format. The equivalent routines (and many more) are also available in double precision: merely delete the prefix x from the instructions and the (optional) reference to DgtMax in their arguments, and make sure that Matrix.xla(m) is loaded and installed instead (or in addition: they won’t bite each other).
A sample spreadsheet illustrating the use of XN instructions for singular value decomposition of a complex, rectangular matrix at high (600-decimal) numerical precision. Just compare this example with the standard Excel fare, which does not even have any functions for eigenvalue analysis of a square matrix with real elements in regular 15-decimal precision. The instruction names can be "read" as follows: the prefix x specifies extended precision, here defined in the argument as the number specified in cell B7; SVD refers to Singular Value Decomposition; U, S and V are the three matrices of the SVD, C signifies that the matrices are Complex; MTH stands for Hermitian (conjugate, adjoint) Matrix Transpose, and xMMultC for extended precision Multiplication of Complex Matrices.
So much new (and, I hope, interesting and useful) material was added that it took me much longer than I had anticipated to finish this third edition. Fortunately, the book is now available from Amazon. Look under R. de Levie Advanced Excel for the large, brighly colored cover and especially for the specification 3rd edition. And even though you will get much more for your money, the Amazon price is still the same.
Click on any of these, or just scroll down to them.
Changes in the third edition
New features on this website
Problems using the MacroBundle in Excel2007/2010
An improved macro for the first derivative
Quasi-contour diagrams
Additional aspects of surface graphs
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
The third (2012) edition of Advanced Excel for Scientific Data Analysis involves a substantial expansion, with 25 added sections, which are all fitted within the earlier framework of eleven chapters. The page count was kept down by increasing the page format from 6" by 9¼" to 8½" by 11". The additional material mostly falls into the following three categories.
(1) Visual aids. The spreadsheet lends itself eminently to graphically visualizing the essence of least squares, viz. finding the minimum in a function using a given criterion. Lars Sillen already introduced a "pit plot" of displaying log(SSR), the logarithm of the sum of squares of the residuals, as a function of one or two parameters, and this approach is readily implemented with Excel's surface plots or with Mapper from my MacroBundle. The shapes of these plots are often alerting us to potential difficulties in the least squares analysis.
(2) Matrix algebra. The rectangular array of spreadsheet cells makes it a natural medium for matrix algebra, displaying all its elements rather than treating matrices as abstract entities. Chapter 10 was therefore expanded by emphasizing the convenience and immediacy of the many additional functions of Matrix.xla(m) in, e.g., eigenvalue and eigenvector analysis, and singular value decomposition (see above example), including its use for rank reduction in chemometrics.
(3) Extended precision. The power of Volpi's Xnumbers has been extended greatly by John Beyers, who has made it suitable for use on spreadsheets, as well as in custom functions, macros, and other subroutines. Moreover, the nomenclature used in the newly renamed XN.xla(m) now closely matches that of Matrix.xla(m), thereby facilitating extended-precision matrix operations on the spreadsheet. Much of chapter 11 was rewritten to accommodate these changes, and to illustrate when extended precision is necessary to obtain reliable answers, even if only to two or three decimals. As before, Advanced Excel for scientific data analysis primarily teaches by example, wherever possible using actual experimental data from the literature, and using worked-out exercises with explicit solutions. Please e-mail me your questions, comments, and suggestions at rdelevie@bowdoin.edu
Click here to return to the Contents or to advance to the Downloads.
Many topics discussed on the website of the first edition of Advanced Excel are now included in its third 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 more than 4000 decimals. As before, new developments that came too late to be included in the latest printed edition will be added here. The number of downloadable files has increased. You will find an enlarged set of macros in the MacroBundle, many of which 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. 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. In addition to the above, this website now also contains several additional, freely downlodable software packages, almost all with open access to their code.
XN.xla(m) has now replaced Xnumbers.dll, but the latter is still available for downloading. Please note that users of Excel 2007/2010 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. However, because XN is much more powerful and much more convenient, I suggest that you convert any XNumbers files you may still have to XN; the conversion is almost trivial.
Click here to return to the Contents or to advance to the Downloads.
Problems using the MacroBundle in Excel2007/2010
Some users of Excel 2010 may experience problems with macros with names that could be misread as cell addresses, specifically LS1, ELS1 and WLS1. The problem is that Excel 2010 may misinterpret those macro names as cell addresses. A give-away was that LS0, ELS0 and WLS0 do not give such problems, because Excel's row numbering starts with row 1, so that the number 0 cannot be misread as part of a cell address.
The names were originally coined because LS0 uses no intercept, while LS1 uses one, and the typographically similar all-letter names LSO and LSI (for Least Squares through the Origin and Least Squares with an Intercept respectively) will simply avoid this problem.
If you are affected by this problem, simply change the names in the short driver macros for LS1, ELS1, and WLS1 to LSI, ELSI and WLSI respectively (and, if you value symmetry, change the corresponding names LS0 etc, into LSO etc.) in the MacroBundle text, and from then on use the new names. (The Macrobundle is accessible in the VBEditor Module; use the Find function to search the MacroBundle, including its InsertMBToolbar.) I thank Prof. Tiny van Boekel and Lena Jankowiak of Wageningen University in the Netherlands for bringing this problem and its solution to my attention.
The latest (2012) version of the MacroBundle adopts these new all-letter names; users who do not experience this problem can keep using the earlier names. Subsequent print runs of my Advanced Excel for scientific data analysis will also use this new notation.
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 called Deriv1 has been written, and has been incorporated in the MacroBundle, as well as its extended-numberlength version xnDeriv1 in the xnMacroBundle. The theoretical basis for this improved algorithm is a nice illustration of the interplay between inaccuracy and imprecision in numerical analysis, and is described in chapter 9 of my book, see in SampleSections2 under sections 9.2.9 and 9.2.10). It can also be found in J. Chem. Sci. 121 (2009) 935-950, freely accessible at http://www.ias.ac.in/chem.sci/Pdf-Sep2009/935.pdf. 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.
In double-precision arithmetic there is little advantage in going to j-values beyond 9, because cancellation noise may then limit 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.
Moreover, keep in mind that all such problems of limited accuracy and large footprints disappear when extended-precision software is used. Likewise, extended pecision obviates the need for analytical derivatives in optimization routines such as the Levenberg-Marquardt algorithm.
Click here to return to the Contents or to advance to the Downloads.
The set of available Mapper macros has been extended to provide two extra types of quasi-contour diagrams. They use stepwise rather than continuous color changes, and therefore display color bands. For a sufficiently large array, of at least 100 by 100 cells, they are considerably faster to make (because they do not require any additional interpolations) than IsoL.xla, and approach a similar visual resolution. For smaller arrays, pixellation will show as serrated edges where IsoL.xla still exhibits clean curves. Samples of these Mapper macros are shown here for a simple 150 by 150 cell test array with gradually increasing numerical values from the top left to the bottom right. The first number in the names of these routines follows the color scheme of Mappern, doubling the number n past the term Mapper to nn yields bands in a single progression, while triple numbers nnn show bands that have maximum brightness in the middle region of the cell values used. Moreover, the old Mapper1 has been modified, and the MBToolbar has been modified to incorporate these new Mapper macros.
Quasi-contour plots for a gradually increasing value from top left to bottom right, for a gray scale (Mapper00), a spectral scale (Mapper11), a red scale (Mapper 22) and a blue scale (Mapper 33).
Quasi-contour plots for a gradually increasing value from top left to bottom right, for a gray scale (Mapper000), a spectral scale (Mapper111), a red scale (Mapper 222) and a blue scale (Mapper 333).
Click here to return to the Contents or to advance to the Downloads.
Additional aspects of surface graphs
Section 1.4 of Advanced Excel 3rd ed. briefly describes 3-D surface graphs. Nenad Jeremic kindly sent me the following information, which I gladly share here.1. When you hold dow the Ctrl key while rotating a 3-D surface graph with your mouse, Excel will continuously plot outlines of the surface. This helps you to fine-tune the effect of rotating the graph.
2. You can add shading to a 3-D chart as follows. After you have made the graph, select a single color in the chart legend box. Right-click on it, select Format Legend Key, go to the Options tab, and select the 3-D shading option check box. The shading will then be applied to all colors, i.e., to the entire graph.
This effect is most useful when the grid lines are fairly widely spaced. If you apply the shading first, and then rotate the graph, the shading also rotates, which can make for a more dramatic effect. The figures illustrate this effect for the three-peak surface diagram of Fig. 1.4.2 in the third edition of my book (AE3) on p. 21. As you can see, it can make these plots more appealing in both color and black & white. 
Colored plot, no shading.
Colored plot, with shading.
Black & white plot, no shading.
Black & white plot, with shading.
Click here to return to the Contents or to advance to the Downloads.
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 (Excel 1997/2003) 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.
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.
Please note that a different set of colors, typically less saturated and with different names, is used in Excel 2007/2010 in the Font section of its Home ribbon. However, the old ColorIndex still works in these newer versions.
Click here to return to the Contents or to advance to the Downloads.
Excel is the most ubiquitous numerical software available, bar none. 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 guesses 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) = x2. 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 + a2 – b2 is not the same as – b2 + a2. 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: (a2) – (b2) = – (b2) + (a2), 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 a2–1*b2 = –1*b2 + a2, 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.
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.
However, there are many situations, such as when large matrices must be manipulated, in which the numerical accuracy during data analysis may be degraded sufficiently to cause it 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 updated and expanded, very powerful XN.xla(m) freeware package of John Beyers, building on the earlier XNumbers.xla of Leonardo Volpi and his team in Italy, has now made it both possible and easy to extend the numberlength limits of Excel macros, and hence their numerical precision, provided that you have access to their code. With this new software addition you can obtain much more accurate results from the spreadsheet, from functions, and from macros, and you can also use XN to test the accuracy of code fragments, i.e., to identify accuracy-limiting code. This freeware, which can be downloaded from this website as well as from its originating website, http://www.thetropicalevents.com/XNumbers60.htm, is a great gift to scientific users of Excel. You will need an unzip routine on your computer.
The approach used in XN 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/. XN allows Excel versions 2000 through 2010 to make calculations with a user-selectable numberlength of up to 4000 decimal places. If no numberlength is specified, XN will use its default setting of quadruple precision, which is usually sufficient. This default value can be user-modified. 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 somewhat slower execution speed (which is usually not noticeable, except at very high precisions) and the (often) higher accuracy of the results. Practical examples will soon be put in the xnMacroBundle. The first macro to go into that bundle, xnLS, is described in detail in section 11.14 of the latest (third) edition of my book.
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, 3rd ed. Wiley 2010. A good intermediate book, with several chemical examples, a somewhat limited use of VBA, and excellent help for Mac users. It excels in describing the differences between Excel 2000/2003 and Excel 2007/2010.
* E. J. Billo, Excel for scientists and engineers, Wiley 2007. An intermediate book with an emphasis on custom functions. 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, 2nd 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, 2nd ed. O'Reilley 2006. 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, but now somewhat dated.
R. de Levie, Advanced Excel for Scientific Data Analysis, 3rd ed. Atlantic Academic 2012. An advanced text for a general scientific audience, with special emphasis on error analysis, least squares methods, Fourier transformation, matrix algebra, and data accuracy, including extended precision that goes far beyond quadruple precision. Assumes basic familiarity with Excel, then uses custom functions and macros to go well beyond the tools provided by Microsoft.
B. S. Gottfried, Spreadsheet Tools for Engineers: Excel 2000 version, 2nd ed. McGraw-Hill 2002. Nice presentation, but (like Bloch) omits VBA. A new edition has been announced.
W. J. Orvis , Excel for scientists and engineers, 2nd ed., Sybex 1996. The classic book in this area, with an excellent index. A must-have, although by now beginning showing its age.
Click here to return to the Contents or to advance to the Downloads.
The contents of the MacroBundle
This is a collection of macros, with many now using 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 most recent incarnation of the MacroBundle is version 10.The macros in the MacroBundle are all open-access and self-documented, and can be downloaded freely and used without any obligation, as long as they are not used commercially. Because they are distributed as a text file, you can readily inspect them. If you dislike them, please let me know why, and preferably suggest ways to improve them. If you like them, please 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, open-access macros, the more people may benefit from them, which is the purpose of making them freely available in the first place. 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 most recent, more general successor, Advanced Excel for Scientific Data Analysis, 3rd ed., Atlantic Academic 2012. 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 sf 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 2n 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 a higher-precision version of 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.
Mapper
Mapper generates color (or gray-scale) maps of a rectangular array of equidistantly spaced data. At present it contains 12 specific sample maps, four using a gradual color change, four with nine discrete color bands, and four with 17 such color bands. The four color schemes used are a gray scale, a color spectrum, a predominantly red scale, and a predominantly blue one. Since these are all open-access, you can easily add your own color scheme if you so desire; the process is described in the MacroMorsel WriteYourOwnBitMap.
Miscellany
ScanF generates an array of a function F(x,y) of the two variables x and y for subsequent use by Mapper or, optionally, of an input list for SimonLuca Santoro's IsoL macro for creating contour maps.Click here to return to the Contents or to advance to the Downloads.
The contents of the other downloads
There are two sample sections: SampleSections1 covers the material through chapter 4, and SampleSections2 the remaining chapters 5 through 11. These contain the preface, table of contents, and one or more complete sections from each of the eleven chapters of the third edition of my Advanced Excel for Scientific Data Analysis. Even out of their chapter context, the latter may give you an idea of what to find in my book. Below are the sections shown.
SampleSections1:
Preface
Contents
Chapter 1: Survey of Excel
Band 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
Chapter 4: Nonlinear least squares
Titration of an acid salt with a strong base
SampleSections2:
Chapter 5: Fourier transformation
Analysis of the tidesChapter 6: Convolution, deconvolution and time-frequency analysis
Chapter 7: Numerical integration of ordinary differential equationsIterative deconvolution using Solver
Deconvolution by parameterization
Time-frequency analysis
The echolocation pulse of a bat
Chapter 8: Write your own macrosThe semi-implicit Euler method
Using custom functions
The shelf life of medicinal solutions
The XN 4th-order Runge-Kutta function
Ranges & arrays
Invasive sampling
Using the XN equation parser
Case study 5: modifying Mapper's BitMap
Chapter 9: Some mathematical operations
A measure of error, pE
A general model (for numerical differentiation)
Implementation (of numerical differentiation)
Chapter 10: Matrix operations
Matrix inversion, once more
Eigenvalues and eigenvectors
Eigenvalue decomposition
Singular value decomposition
SVD and linear least squares
Summary
Chapter 11: Spreadsheet reliability
The error function
Double-precision add-in functions and macros
The XN functions for extended precision
Filip, once more
The xMacroBundle
The xMacroBundle is still carried here, but will be removed soon to make place for its updated XN version, which is useful on the spreadsheet, in functions, and in macros, both in Excel2000/2003 and Excel 2007/2010. 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."
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 will be updated to the third edition shortly. 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 previous, second edition, and will be updated shortly to the third 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
Data analysis* ReadActiveCell
* ReadActiveArray
* InputBoxForNumber
* InputBoxForCell
* InputBoxForRange
* InputBoxForArray
* OutputASingleValue1
* OutputASingleValue2
* OutputSeveralValues
* OutputAnArray1
* OutputAnArray2
* OutputAnArray3
* ControlOutputFormat
* FromArrayValuesToRangeValues
* PreventCellOverwrite
* PreventColumnOverwrite
* RoundingSpreadsheet & macro management
* 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
Click here to return to the Contents
Supplemental information
Data supplementing a paper on spectrometric mixture analysis in J. Chem. Sci. 121 (2009) 617-627 are available here for downloading.
Click here to return to the Contents
The downloads
| Downloads: | file format: |
| MacroBundle | .doc |
| xMacroBundle | .doc |
| SampleSections1 | .pdf |
| SampleSections2 | .pdf |
| MacroMorsels | .doc |
| xMacroMorsels | .doc |
| GNU General Public License | .doc |
| SampleData | .doc |
| SampleFunctionsAndMacros | .doc |
| Errata for Advanced Excel, second (2008) edition | .doc |
| Errata for How to use Excel in analytical chemistry | .doc |
| ContourMap IsoL courtesy of Simonluca Santoro | .zip |
| ContourMap IsoL 2010 courtesy of Simonluca Santoro | .zip |
| RandomPlot courtesy of Leonardo Volpi | .zip |
| Matrix.xla courtesy of Leonardo Volpi | .zip |
| BigMatrix.xla courtesy of Leonardo Volpi | .zip |
| XN.xla courtesy of Leonardo Volpi & John Beyers | .zip |
| XN.xlam courtesy of John Beyers | .zip |
| Optimiz courtesy of Leonardo Volpi | .zip |
| MathParser documentation courtesy of Leonardo Volpi | .pdf` |
| J. Chem. Sci 121 (2009) 617-627 supplemental data | .xls |
last updated: December 2010