The Co Laboratory

Discovering New Concepts in Interfacial Reaction and Engineering for
Template Synthesis of Nanomaterials, Encapsulation, and Dynamic Control of Cell-Biomaterial Interfaces

General Instructions for Using Custom Excel Spline,
Bootstrapping, and Global Optimization Routines

The need to interpolate between experimental data points, estimate errors, and find global minima (maxima) of functions following complex calculations on large sets of data frequently arises in Chemical Engineering analysis and design. Since MS Excel spreadsheets are ideal for a large number of Chemical Engineering calculations, I am offering a set of add- in functions for performing cubic-spline interpolation and smoothing that you may find useful. Two Excel Visual- Basic routines are also provided for performing error-analysis via bootstrapping and automating repeated local optimizations (MS Solver) from multiple starting points.

  1. If you haven't done so already, install Solver from the Excel 97, XP or 2003 version of MS Office CD-ROM.

  2. Excel 97 users must update their software with MS Office97 Service Release 1 (SR-1) and Service Release 2b (SR-2b). Install both patches before proceeding with the following installation.

  3. Download CustomAnalysisFunctions.zip

  4. Extract into a new directory "C:\ExcelDLL" (no other path or directory is acceptable)

  5. Open Excel

  6. Go to Tools, Add-Ins

  7. Select Browse; highlight Spline97.xla; Click O.K.  This installs the interpolating, smoothing, and cross validation cubic spline functions.

  8. Select Browse; highlight BootStrap97.xla; Click O.K.  This installs the bootstrapping and global optimizer macros.

  9. Close, then restart Excel.

  10. All of the functions operate through the use of Array Formulas and the following introduction may be useful.

Introduction to Array Formulas

Array formulas are useful, but very seldom utilized by most Excel users. Array formulas can be used to perform multiple calculations that return multiple results. For example, to perform matrix multiplication on two 3x3 matrices,

Notice that the Excel MMULT function contained in cells I1:K3 is surrounded by {braces}.

Perhaps the easiest way to enter this array- formulas is:

  1. Using a combination of mouse and keyboard, enter "=mmult(A1:C3,E1:G3)" in cell I1. Functions can also be inserted without having to memorize the syntax or function names using the Excel menu item Insert - Function.

  2. Starting from I1, use the mouse to highlight the block I1:K3.

  3. Press F2 or click on the formula bar.

  4. Press CTRL+SHIFT+ENTER.

The cells of the array- formula cannot be changed independently. To select an entire array formula, select one of the constituent cell, then press CTRL+/.  To learn more about array- formulas, look in the Excel help index for array-formulas.  At first, array-formulas may be difficult and cumbersome to use, but practice makes everything easier. The extensive use of array-formulas minimizes the communication overhead between Excel and the custom Fortran DLLs and enables instantaneous spreadsheet recalculations in most cases.