ChemQuantFunctions Excel Add-In
A Microsoft Excel Macro-Enabled Add-In 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 non-linear 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 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 Version 1.7 and documentation here.***
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:
- 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.
- 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.
MacroBundle12Propagation 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 "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.
- 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.
- 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)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.
- 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 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.
- 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.
f_test Performs an F-test 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 non-excluded 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 ctrl-clicking 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.
- 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.
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 6-1
- 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 6-1
- 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 2-3
- 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 2-4
- 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 2-5
- 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 2-7
- Conf_Interval(prob, std, df) Returns the confidence interval for a given probability (prob), standard deviation (std) and degrees of freedom (df).