ChemQuantFunctions Excel AddIn
Section Navigation
Related Majors
Related Links
ChemQuantFunctions
A Microsoft Excel MacroEnabled AddIn for Quantitative Analysis and Chemical Equilibrium
***Download a zipped folder containing ChemQuantFunctions.xlam Version 1.7 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 nonlinear least squares fitting).
 MaxwellStatsUncertaintyPackage, by Maxwell Brown ('20), Whitman College adds macro tools for additional statistical tests, propagation of error and cell formating.
 RubsamenYumibePackage, by Michael Rubsamen ('22) and Lauren Yumibe ('22), Whitman College adds additional constants (e.g. Kw, pKw) and table lookup values for glassware uncertainty and water density.
For a full list of features, see below.
Please note that MacroBundle12 and EngCel can be obtained as separate AddIns from http://www.beingbrunel.com/engcelcoloursnamesandmore/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 Version 1.7 and documentation here.***
Instructions
Once downloaded, saved to the desired folder, and extracted, you must load the addin into Excel. The method for loading Excel addins 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 inline 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.
 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 "SavitzkyGolay" moving polynomial fit. ELSfixed uses a userselected, fixedorder polynomial, ELSauto selfoptimizes 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 userassigned 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 Ftest) 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.
 SolverAid provides uncertainty estimates (standard deviations, the covariance matrix, and optionally the matrix of linear correlation coefficients) for Solverderived parameter values.
 SolverScan lets Solver scan a twodimensional array of parameter values. It requires that Solver.xla is installed.
 ColumnSolver applies Solver linebyline to columnorganized data. It requires that Solver.xla is installed.
 FT is a generalpurpose Fourier transform macro for forward or inverse Fourier transformation of 2^{n }data where n is an integer larger than 2.
 Gabor provides timefrequency analysis.
 Ortho yields GramSchmidt 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 nonnegative.
 Deriv uses central differencing to find the first derivative of a function.
 Deriv1 is a higherprecision 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.
 Semiintegrate & semidifferentiate comprises two small macros for cyclicvoltammetric (de)convolution assuming planar diffusion.
 Mapper generates color (or grayscale) 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 openaccess, you can easily add your own color scheme if you so desire; the process is described in the MacroMorsel WriteYourOwnBitMap.
 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
MaxwellStatsUncertaintyPackage
Statistical Tests

f_test Performs an Ftest by prompting you for the confidence level, standard deviations, and D.F. The conclusion is put relative to the currently selected cell.

confidence_interval Creates a confidence interval by prompting for the mean/stddev, confidence level, and sample size.

grubbs_test Performs the grubbs test for outliers by prompting for information about the data set. produces Grubb's test values to the right of a column of data and then a second column of nonexcluded data points.
Propagation of Error

sum_with_error Performs the summation of any number of cells and ranges (both positive and negative terms). You can select multiple cells by ctrlclicking when the prompts pop up. Press escape to skip a step if you don't need to include positive or negative terms. Each cell you select should be a VALUE and have an uncertainty in the cell directly to its right. The result is placed where your selection was when you ran the macro. (All of the macros below work with these same mechanics.)

product_with_error Works the same way as sum with error, but performs a product with numerator terms. Denominators not implemented yet, but if you include every term as if it were a part of the numerator, the value formula can be adjusted pretty easily to do division and the error term will reflect the correctly propagated result without further editing.

power_with_error Performs the power of a base and exponent where there is no error in the exponent (ie x^a).

exp_with_error Performs e^x where x is an exponent with associated error.

exp10_with_error Performs 10^x where x is an exponent with associated error.

log_with_error Performs the common logarithm log10(x) where x is an antilogarithm with associated error.

ln_with_error Performs the natural logarithm ln(x) where x is an antilogarithm with associated error.
Error Formatting
 format_with_error Format a value and uncertainty according to the real rules for reporting sig figs. Treats the selected cell as the value and the cell to its right as the uncertainty. Currently, the exponential form doesn't take into account reporting an additional sig fig if the first digit is 1, but the decimal form does do this.
 format_exponent_with_error Works just like format_with_error but tries to use exponential notation universally.
RubsamenYumibePackage
Constants and Formatted Equations
 Kw() Returns value of water dissociation constant, Kw = 10 ^ 13.997
 pKw() Returns value of negative log of water dissociation constant, pKw = 13.997
 temp_dependent_Kw(temp_C) Returns Kw value at a given temperature (in deg. C). Source: Daniel C Harris Quantitative Chemical Analysis, 9th Ed. Table 61
 temp_dependent_pKw(temp_C) Returns pKw value at a given temperature (in deg. C). Source: Daniel C Harris Quantitative Chemical Analysis, 9th Ed. Table 61
 Kw_equil() Returns Kw equilibrium expression: Kw = [H+][OH]
 Ka_generic() Returns generic Ka equibrium expression for H+ and A
Table Lookup Values
 volflask_unc(flask_vol) Returns the uncertainty of a Class A volumetric flask. Source: Daniel C Harris Quantitative Chemical Analysis, 9th Ed. Table 23
 Fixed_transfer_pipette(pipette) Returns the uncertainty of a transfer pipette of a given volume (in uL). Source: Daniel C Harris Quantitative Chemical Analysis, 9th Ed. Table 24
 Fixed_micro_pipette(pipette) Returns the uncertainty of a micropipette of a given volume (in uL). Source: Daniel C Harris Quantitative Chemical Analysis, 9th Ed. Table 25
 water_density(temp_C) Returns the density of water at a given temperature (in deg. C). Source: Daniel C Harris Quantitative Chemical Analysis, 9th Ed. Table 27
Statistics Function
 Conf_Interval(prob, std, df) Returns the confidence interval for a given probability (prob), standard deviation (std) and degrees of freedom (df).