ChemQuantFunctions

A Microsoft Excel Macro-Enabled Add-In for Quantitative Analysis and Chemical Equilibrium


***Download a zipped folder containing ChemQuantFunctions.xlam and documentation here.***

ChemQuantFunctions.xlam adds a number of useful features to Microsoft Excel for students. The software combines several useful extensions:

  • QuantFunctions, by Nathan Boland. QuantFunctions adds a number of useful Excel functions including the quadratic formula, useful physical constants (e.g. Avogadro's Number), a Grubb's Test table lookup, and a chemical species alpha fraction calculator.
  • EngCel, by Thomas Wallace. EngCel provides a convenient way to quickly subscript, superscript and add symbols.
  • MacroBundle12, by Robert deLevie. MacroBundle12 provides a number of useful tools for statistical analysis, Fourier transform, and calculus (including uncertainty estimates for non-linear least squares fitting). 

For a full list of features, see below.

Please note that MacroBundle12 and EngCel can be obtained as separate Add-Ins from http://www.beingbrunel.com/engcel-colours-names-and-more/and http://www.bowdoin.edu/~rdelevie/excellaneous/ , respectively.

The software is free and is released under a GNU GPLv3 copyright license.

***Download a zipped folder containing ChemQuantFunctions.xlam and documentation here.***

Instructions

Once downloaded, saved to the desired folder, and extracted, you must load the add-in into Excel. The method for loading Excel add-ins can be found here.


ChemQuantFunctions Features list:

QuantFunctions

  • F() Returns Faraday's Constant in C/mol
  • e() Returns the elementary charge in C
  • k() Returns Boltzmann's constant  in J/K 
  • H() Returns Planck's constant in J*s 
  • GasJ() Returns the Gas constant in J/(mol*K) 
  • GasLatm() Returns the Gas constant in L*atm /(mol*K) 
  • Avogadro() Returns Avogadro's number
  • QUADRATIC() Returns the two x values from solving the quadratic equation (a*x^2 + bx + c = 0) as a 1x2 array.
  • DAVIESG() Returns the activity coefficient (gamma) calculated using Davies Equation
  • GINV() Returns the critical Grubbs value for a dataset (two tail).
  • AlphaFrac() Returns the fractional composition (alpha) for an weak acid or base species at a given pH and protonation level and pKas.
  • SciExp() Returns number in text scientific notation format (for labels).
  • UncInX() Returns the uncertainty in an unknown X value determined from a calibration curve.

EngCel

  • Convenient in-line super/sub script and symbol notation based on LaTeX. Accessed by entering the pipe symbol "|" at the begining of a cell.
  • Fast insertion of formatting templates
  • Fast variable naming
  • Apply coloring to cells containing functions depending on their use/relationships.

MacroBundle12

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, and as many independent variables as may fit your fancy. LSO forces the fit through the origin, LSI 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. It requires that Solver.xla is installed.
  • ColumnSolver applies Solver line-by-line to column-organized data. It requires that Solver.xla is installed.
Transforms
  • FT is a general-purpose Fourier transform macro for forward or inverse Fourier transformation of 2data where 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. 
  • 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. Can be used in Excel 2000/2003 and in the Developer ribbon of Excel 2010.
  • RemoveMBToolbar