Physical Chemistry site graphic

Chemacro for Microsoft Excel: User's Guide

Introduction

Microsoft Excel is a powerful aid for any numerical calculation. It is a spreadsheet program which can produce data based on virtually any mathematical relation among any sort of data. It can plot (chart, in Excel language) data, and it can be automated to carry out repetitive or tedious tasks by means of commands called macros. Chemacro is a special kind of Excel macro document called an Add-In, which contains the instructions for tasks routinely needed in calculations in the physical sciences and engineering. Chemacro operates out of sight, as if it was part of Excel itself. Chemacro is easy to learn: there are only four new menu commands (all found under the View menu) and two new functions associated with Chemacro.

The commands can:

  • teach Universal Physical Constants to your spreadsheet (called a Worksheet by Excel),
  • look up numerical conversion factors for over 130 units,
  • perform a simple linear least-squares fit to selected data and plot them and the fit, and
  • produce a simple x,y scatter plot of experimentally measured or computed data.

The functions, which are the true workhorses of Chemacro, can

  • convert a quantity in any combination of the 130+ units known to Chemacro into any other dimensionally equivalent combination or directly into SI base units,
  • decompose a combination of units into its base SI equivalent, and
  • compute a molar or molecular mass given a chemical formula.

As an example of the power of these functions, consider the following problem from dimensional Hell: what is the pressure in pounds per square inch exerted by 1.643 lb of SF6(g) at 188 °F in a container which measures 3.25 in by 2.6 ft by 1.1 m?  The math is simple enough: P = nRT/V, but conversion of the data to compatible units is not.  Here’s how the Chemacro functions MMASS and UNITS make the problem trivial to solve.  The worksheet below shows the input data and the answer, while the following view of the same worksheet shows the formulas behind the numbers.

Worksheet showing values

Worksheet showing formulas

Column A holds descriptive text to remind us what the numbers mean.  Column B holds both numerical constants and computed results, shown here in what Excel calls a General numerical format.  We could instruct Excel to display the answers with the correct number of significant figures, but for now, we’ll ignore that option.  The formulas in the second picture get to the heart of the matter.  In row 1 we have the SF6 mass expressed in pound units.  Row 2 converts this into moles by means of the UNITS and MMASS functions.  All formulas are entered into Excel with an opening equals sign.  Following the = here comes UNITS(B1,"lb"), which is a function call (just like SQRT(2) is a function call for the square root of 2).  You can type the Chemacro functions into Excel in lower case letters, and Excel will convert them to upper case as required.

Here, we are asking Chemacro to take whatever is in cell B1 (it had better be a number, or we will get an error) and convert it from pound units (lb) to base SI units (kg here).  Next, we divide this mass by the molar mass of SF6 in kg/mol units to get moles of SF6.  This is done by the MMASS("SF6") function which takes a chemical formula and returns the corresponding molecular mass.  (MMASS can return an answer in units other than kg/mol as we will see.)

In row 3, we enter the temperature in °F and convert this to SI kelvin temperature units in row 4.  Finally, row 5 computes the pressure from P = nRT/V again using UNITS.  Note that the first argument to UNITS is itself a computed value (as opposed to a constant or a reference to another worksheet location).  In detail, B2*RSI*B3/(3.25*2.6*1.1) means “take what’s in cell B2, multiply it by the quantity known by the name RSI, multiply that by what’s in cell B3, and divide the whole thing by the product 3.25*2.6*1.1.”  This is nRT/V in our funny units.  RSI is Chemacro’s name for the Universal Gas Constant (R) in SI units.  Prior to entering these formulas, the worksheet was taught the names of Universal Physical Constants by the Chemacro command Constants… which is found under the View menu.  Thus, instead of remembering R = 8.314 472 J mol–1 K–1, we type RSI (and if we forget that RSI is the name for R, we can also look it up with the Constants… command).

The second argument to UNITS is the unit combination for nRT/V as we have it.  Note that mol and K units could be canceled, but this is neither necessary nor such a great idea, since we can follow nRT/V factor by factor and see the original units.

Note that the UNITS function has taken on a third argument.  If this argument is omitted, UNITS returns a “pure SI” value (explained in detail below).  If the third argument is specified, UNITS continues the conversion, checking first to see that these units are compatible with those of the second argument (which must always be present).  Here, the third argument expresses the pressure units we want for our answer, psi.  If we were not sure Chemacro knew what psi stands for, we could turn to the Units… command under View and look it up.  This would tell us not only that psi is known to Chemacro, but it would tell us also the conversion factor Chemacro uses in the UNITS function as well as other information about psi.

That’s all there is to it.  Moreover, these computed values are linked in that we can change, say, the initial temperature, and the pressure will immediately change.  We could as easily enter a table of temperatures and compute a table of corresponding pressures.

Go to top

Plotting

Suppose we have computed a complicated function y = f(x) as a series of points and would like to see it plotted.  Excel can quickly generate a chart of selected data from a worksheet, which the Quick Plot Chemacro command (in the View menu) automates for you.  Quick Plot won’t give you the most beautiful plot Excel is capable of producing, but it will give you plotted points, axes, and a grid, which is often good enough.  It is also a good starting place from which to make a prettier plot once you learn Excel’s chart commands.

For example, consider the function y = (1 + x + x2/3) e–x (which is the form of something called the overlap integral between two particular H atom wavefunctions a distance x apart).  What does this function look like over the range 0 ≤ x ≤ 4?  The worksheet below tabulates x values in column D and corresponding y = f(x) values in column E.  Once the x value column is entered, we enter the function, referring to the first x value by cell co-ordinate.  (You would type =(1+D2+D2^2/3) *EXP(-D2) into cell E2.)  Then, with the mouse clicked in cell E2, we drag down the y column to the last row of the x column (E2 through E42) and use the Fill/Down command under the Edit menu.  This fills the column with the formula, automatically adjusting the x references as it goes.  The table looks like this (in a split pane view):

Next, we select all the data by clicking in the top x-value cell (D2) and dragging to the bottom y-value cell (E42).  All the data should now be highlighted.  We select Quick Plot from the View menu, and the plot below appears.  This chart can be copied into a worksheet or edited as desired with axis labels, different plot symbols, etc.

Now suppose we have a set of (x,y) data that we want to fit to a linear equation of the form y = mx + b where m is the slope and b is the y axis intercept.  The following worksheet snippet does just that, using the temperature in °C as x and the vapor pressure of water in torr as y.  These data are from the CRC Handbook of Chemistry and Physics, 67th edition, p. D-190, and could be converted to any other units with the UNITS function.  As with Quick Plot, x values are in one column, and y values are in the adjacent column to the right.  The data to be fit are selected, and the Linear Fit command is selected from the View menu.  The picture shows that the selected data used in the fit is outlined on the worksheet (Quick Plot will do the same) and that the best slope and intercept parameter values appear below the data.  (If either or both appear as a string of # characters, this means the number won’t fit in the cell.  Just widen the column a bit by dragging the column dividing line at the top of the worksheet between two column coordinate labels.)

Below the best values are the statistical errors of each parameter, expressed at the 95% confidence limit, which means that repeated measurements of the same physical quantity has a 95% chance of having parameter values in the range best parameter ± 95% error as long as the measurement errors are random and the physical quantities are truly related through a linear equation. If an error is larger than the magnitude of its parameter, the parameter is statistically undetermined and either meaningless or essentially zero.

A common associated error measure is the standard error, which is the 95% error divided by a quantity known as “Student’s t”.  (“A. Student” was the pseudonym used by Gosset when he published important papers on data analysis in the early 1900’s.)  This number decreases with the number of data points in the fit, and it is displayed on the worksheet as well.  Finally, Chemacro shows the standard deviation of the fit, defined for a fit of N points as the square root of the sum of the squares of the residuals (the actual y value minus the value predicted by the fit, at each x value) divided by the number of data points minus 1.  In general, the smaller this quantity, the better the fit, so long as all the coefficients of the fit are statistically determined.

Chemacro will give us a dialog box of plotting options before showing the graph.  Options include plotting the data and/or the fit line (the default is to plot both), axis label text, and a plot title.  Chemacro will use any text at the top of the x and y columns (which should not be selected when requesting the fit) as suggestions for the axis labels.  The suggested plot title is the worksheet window’s title.  That was changed here to give us the plot below, and the default axis limits were adjusted to make the data span the graph.  Note that the straight line representing the fit is drawn from the minimum x value to the maximum x value.

A chart window can be expanded to fill the screen; the picture above is the default size.  Goodies relating to the look of the plot can be changed using various Excel commands that appear when a chart window is active.  Double-clicking any part of a chart generally opens a dialog box showing options for that part of the chart.

Go to top

Running Chemacro

The easiest way to invoke Chemacro is to place it in the  folder of Excel Startup items before you launch Excel.  You can also open Chemacro directly from the File menu (and note that the file is called Chemacro.xla so that the Windows version of Excel recognizes it correctly).  Chemacro itself is hidden from view and becomes an integral part of Excel itself.  This current version has been tested on Macintosh versions of Excel from Excel 98 through Excel 2004 on a Mac and on some, but not all, Windows versions.  (A minor bug crops up in some Windows version when using the Linear Fit command.  A warning box appears several times telling you that Excel could not empty the clipboard.  This is harmless.  Just keep dismissing this warning until the graph appears.)

If you open a worksheet that uses Chemacro functions and/or that was taught Chemacro names for Universal Constants, those functions and names will not work unless Chemacro is loaded as well, as it will be if you have placed Chemacro in the start-up folder as suggested above.

Similarly, if you give someone a copy of a worksheet that uses Chemacro, give them the Chemacro.xla file as well or they won’t be able to run your worksheet.

Errors from Linear Fit and Quick Plot commands will generally be reported to you in alert message boxes.  The Constants… and Units… commands do likewise, with standard printing error messages if you request a printed version of the Physical Constants reference table.  The Chemacro MMASS and UNITS functions return error messages as text directly to your worksheet.  For this reason, is a good idea to use these functions alone in a single cell.  If you use a Chemacro function in a more complex formula (as we did in cell B2 of the first example), any error text returned by the function will result in the Excel #VALUE! error constant, since you can’t use error text in an arithmetic way.  If you see the Excel error alert box telling you your formula is in error, it may mean that you forgot to put quotation marks around a text argument—either a chemical formula for MMASS or a units string for MMASS.

The rest of this manual has further detailed instructions about the various functions and commands in Chemacro.  Once upon a time, many Excel versions ago, this text formed part of a help file for Chemacro.  Unfortunately, adding customized help to Excel is no longer very easy, but then using Chemacro is very easy, so that with a little practice, you won’t need to refer to this manual.

Go to top

A Note About SI Units

There are seven so-called base units in the SI (Le Système Internationale d’Unités) scheme:  kg (kilogram) for mass, m (meter) for length, s (second) for time, A (ampere) for electric current, cd (candela) for luminous intensity, mol (mole) for amount, and K (kelvin) for thermodynamic temperature.  Many defined units have conversion factors of unity: 1 J (joule) = 1 kg m2 s–2, but many common units have conversion factors into base units that are not unity.  Some of these factors are exact by definition: 1 in (inch) = 0.0254 m exactly; others are not.  Some have the same trivial name (calorie, for example), but exist in different flavors with different conversion factors: 1 cal (thermochemical calorie) = 4.184 J (exactly), but 1 calIT (International Steam Table calorie) = 4.186 8 J (exactly).  Chemacro will use the most common unit symbol for the most common conversion factor, but if you are in doubt, look up the conversion factor in the Units… dictionary.

While 1 J ≠ 1 erg ≠ 1 cal ≠ 1 Btu, etc., all these units represent energy.  Similarly, in, ft, m, etc., all represent length.  In short, they are said to decompose into the same SI base units (kg m2 s–2 for energy, m for length, etc.).  The UNITS function will do this decomposition for you if you omit the first argument.  For example, UNITS(,"psi","atm") will decompose both psi and atm into kg m–1 s–2, which is pressure.  (Note that the first comma must be there to indicate no first argument.)  A particularly worthwhile use for this feature places this form of UNITS in a cell adjacent to that which actually does the conversion.  For example, if cell A1 does the conversion of 3.25 g cm–3 into SI base units, place =UNITS(,"g/cm^3") in cell B1, and the unit conversion reminder g/cm^3–>kg^1m^-3 will appear.

            The MMASS function with no arguments beyond the chemical symbol returns the molecular mass in kg/mol units.  If a second argument is added and it is anything other than "kg", "SI", 1, or TRUE, Chemacro will return the molecular mass using g for mass.  An optional third argument controls the amount unit.  If it is anything other than "mol", "SI", 1, or TRUE, Chemacro will return the molecular mass using molec (i.e., “per molecule”) as the amount unit.  Thus, MMASS("H2") and MMASS("H2","SI","mol") both return the mass in kg of 1 mol of H2 while MMASS("H2",,0) returns the mass in kg of a single H2 molecule.

Go to top

Chemacro Reference

Constants…

This menu item displays a dialog box containing a scrolling list of the Universal Physical Constants known to Chemacro by their 1998 adjusted values.  The scroll box shows the name of the quantity, its SI units, and the name by which Chemacro knows the quantity.  For example, the first entry is “Atomic mass unit/kg (amu),” which means the atomic mass unit constant, measured in kg units, is named amu.

The dialog box also has check boxes which can be selected to:

  • teach the names to the currently active worksheet and/or
  • print the constants, their names, values, and units on the currently selected printer.

Make your choices and click the OK button.

If you teach the names to your worksheet, you can refer to the constants by name in formulas.  Use this menu item to look up the names at any time or to print them for reference.

     Example:  The potential energy (in J units) between two electrons 1 Å (1E-10 m) apart in vacuum would be calculated from Coulomb’s Law by the following formula:

 =e^2/(4*PI()*e0*1e-10)

     Here e is the elementary charge, PI() is the Excel function which returns the value for pi, and e0 is the permittivity of vacuum.  Note that the constant names a0 (the Bohr radius), e0 (the permittivity of vacuum), and mu0 (the permeability of vacuum) all end in the character zero, not the letter “oh.”

Go to top

Units…

This menu item is a reference point for the units conversion functions of Chemacro.  When selected, a dialog box appears showing a scroll box of the conversion factors known to Chemacro along with the unit symbol, such as “Mole {mol},” which means the mole unit is known by the symbol mol.  It also lists the powers of 10 prefixes known to Chemacro (such as k, µ, M, etc.).  Most of the symbols will be familiar, but you should refer to this list to check whenever you seem to be getting unusual values or errors in using the UNITS function (described below).  If you need atomic or molecular mass data, see the MMASS function reference below.

Once you select a unit (it will be highlighted when selected), you can click the Look Up button (or press Return) to get more information on the factor.  A new dialog box will appear which gives you the name, symbol, numerical conversion factor, SI equivalent units of that factor, and the physical quantity it represents, such as volume, energy, etc.  The equivalent units are expressed in terms of powers of the seven fundamental SI units: kg, m, s, A, cd, mol, and K.  (The obscure one is cd; look it up as an exercise if you don’t know what it is!)  The Done button will return you to Excel, while the Another button will recall the first dialog box from which you can look up another factor.

Go to top

UNITS function reference

These symbols and factors are used by Chemacro’s UNITS function, which is a powerful utility for dealing with unit conversion problems.  At its simplest, UNITS will convert a number from any units known to Chemacro into its SI equivalent.  For example, the standard enthalpy of formation of HCl(g) is –22.020 kcal/mol.  To convert this to SI units (J/mol), enter

 =UNITS(-22.020,"kcal/mol")

and the answer (–92131.68) will be returned.  If you would prefer kJ/mol units, add a third argument:

 =UNITS(-22.020,"kcal/mol","kJ/mol")

and the value –92.13168 will be returned.  If you want to see if two unit strings are consistent (i.e., represent the same SI equivalent units), omit the first argument.  Thus

 =UNITS(,"kcal/mol","g*cm^2/s^2*molec")

will return

kcal/mol–>kg^1m^2s^-2mol^-1; g*cm^2/s^2*molec–>kg^1m^2s^-2mol^-1

which is the SI unit decomposition of each argument.  Note how powers of units are represented in the arguments to the example above: all negative exponent units are represented by symbols and powers to the right of the / sign.  Powers must be single digit positive integers.  Purely negative power units are represented as, for instance, “1/m^3” for m^-3.  Just start the unit argument with “1/” followed by units with positive powers.

Fractional powers can be treated in the following way.  Suppose you have a rate constant in M^(1/2)/s units (M is mol/L, or molarity), and you need the value with concentration units of molecules per cm^3.  Enter (assuming the pure number part of the rate constant is 4.2)

 =SQRT(UNITS(4.2^2,"M/s^2","molec/cm^3*s^2"))

In other words, square the quantity and units, convert, then take the square root of the answer.

Temperature conversion is treated uniquely.  If both unit arguments are purely temperature (or if the third argument is omitted, implying "K"), the conversion is done using correct additive constants when needed.  Thus

 =UNITS(32,"°F","°C")

will return 0, since 32 °F = 0 °C.  But if other units appear in the arguments, the additive constants are omitted.  Thus, if you have a quantity in, say, “Btu/°F” units and need the SI equivalent (J/K), only the factor 5/9, which relates the size of the °F degree to that of the K degree, will be used in the temperature unit conversion.

Power of ten prefixes can be used freely.  There are instances where UNITS will return a value that is not what you want due to prefix confusion, but these are rare and fairly obscure.  For example, if you want “peta-are” units (1E12 a = 1E14 m^2) and enter “Pa,” you will get the pascal (Pa) unit conversion instead.  Likewise, “min” matches minutes, not milli-inches.  (Use dcin, “deci-centi-inches,” if you must!)  UNITS first looks to see if the entire symbol is in its table.  If not, it assumes the first character is a prefix, evaluates it, then tries to look up the rest of the symbol.  If this fails, the second character is assumed to be another prefix, and so on until success or failure is achieved.

While UNITS can be used in a more complicated formula as any other function can, it is recommended that a single worksheet cell be assigned to the units conversion.  This not only lets you see the converted number, it also allows you to see any error message UNITS might return if you have used either an unknown unit symbol or requested inconsistent unit conversion such as “m” to “kg.”  Should conversion fail due to inconsistency, just delete the first argument to see the SI equivalents of your units.

Go to top

MMASS function reference

MMASS is another units related function which can compute atomic or molecular masses in SI (kg/mol), g/mol, kg/molec, or g/molec units.  (The abbreviation “molec” means molecule while “mol” means mole.)  Chemacro knows the names and 1996 adjusted atomic masses of all currently known elements (along with D for deuterium and T for tritium).  For radioactive elements, the atomic mass of the most stable isotope is returned.  At its simplest, MMASS is used as follows:

 =MMASS("Al2(SO4)3")

which returns the molar mass of aluminum sulfate in kg/mol units (0.34215388).  Chemical formulas are entered in an obvious fashion, but all ionic charges must be omitted and any ligands must appear as in the following, which finds the SI molar mass of Fe+3•6H2O, the hexahydrate of the iron(III) ion:

 =MMASS("Fe(H2O)6")

MMASS optionally takes two other arguments.  The first of these tells MMASS whether or not the base SI unit for mass, kg, is desired.  If this argument is "kg", "SI", TRUE, 1, or omitted, kg will be used.  If it is anything else, (such as 0, FALSE, "g", etc.), the g unit will be used.  The last argument controls the amount of substance.  If it is "mol", "SI", TRUE, 1, or omitted, the answer will be per mole.  If anything else, the answer will be per molecule.

As with UNITS, MMASS returns error messages (such as an unknown symbol, unbalanced parentheses, etc.) and is thus best used in a cell by itself.  A convenient use for both functions would enter the value (for UNITS) or the formula (for MMASS) in a cell by itself.  An adjacent cell might contain descriptive text (such as “density of water, g/cm^3”) and another cell would contain the function with a reference to its argument.  For example, if cell A2 contains the density value and B2 the descriptive text, then cell A3 could contain =UNITS(A2,"g/cm^3") and B3, the text “density of water, kg/m^3” or B3 could contain the formula =UNITS(,"g/cm^3") which would evaluate to g/cm^3–>kg^1m^-3.

Go to top

Linear Fit

To calculate a linear least-squares fit to a set of (x,y) values, place the x values in one column and the corresponding y values in the adjacent column to the right. Select all points in both columns by dragging from the first x value (top row) to the last y value (bottom row), then select this menu item.

Once the values for the best slope and intercept have been computed, a dialog box will appear giving you check box options for graphing the data and/or the computed straight line.  Check the choices you wish (or click the Cancel button if you do not want a graph), enter the X axis, Y axis, and Title text you want, and click the OK button.  Any text you place at the top of your columns of data on your worksheet will be suggested as axis label text, and the name of your worksheet window will be suggested as the graph title.  Chemacro will then generate the graph.  Once the graph appears, it can be saved, printed, or edited as you wish.

On your worksheet, the data used in the calculation will be outlined and the slope and intercept parameters will appear at the bottom of your data columns.  If you also request a graph of the fit line, the two points used by Chemacro to define the line on the graph will appear as well.  These use the minimum and maximum x values to compute the corresponding y values for the line.

Remember that the graph and your data are linked; if you change a data value, that change will be reflected on the graph.  The fit parameters, however, are not linked to your data!  If you change data values, you must recompute the fit.

Go to top

Quick Plot

A quick and simple graph of (x,y) data can be generated if you arrange the x values in one column, the corresponding y values in the column to the right, select all the data you want to include in the graph, and select this menu item.  This is often useful if you want a quick visualization of the data, or it can be the starting point for the generation of a more elaborate graph.  The graph window that appears can be saved, printed, or manipulated as you wish, and it is linked to your data so that any plotted values you subsequently change will also change position on the graph.

Go to top