Using Excel Functions:

We have already seen how Excel can be used to perform simple calculations by typing equations into cells within the spreadsheet. Obviously, we would want to perform calculations using basic mathematical functions and constants, such as sin x, log y, or π. In this section, we will use Excel's built-in functions to perform elementary calculations. Functions are entered in cells as part of an equation; remember that equations in cells always begin with an “=” sign. We will also use the techniques learned in the preceding exercises to create a series of values, fill cells in the same column with the same formula, and perform calculations using both absolute and relative referencing.

Exercise 1: The LOG and LN functions

A number of analytical techniques require measurement data to be transformed in some manner before a calibration graph can be constructed. One common example is potentiometry, in which the measured response (electrode potential, E Volts) is related to the logarithm of the corresponding ion activity (a) or concentration (C) via the Nernst equation. For a simple electrochemical cell involving a single metal species being reduced at the indicating (measured) electrode, the equation for the reaction for Mn+ + ne can be written as:

1. Open a new Excel™ worksheet and enter the text `C (mol/L)` in cell `A1`, then create a series of concentration values from 0.00 to 1.00 mol/L in increments of 0.05 mol/L starting in cell `A2`.
2. Enter the text `ln C` in cell `B1`, then enter the equation `=LN(A2)` in cell `B2`; use Edit→Fill→Series... or click-and-drag the small square on the bottom-right corner of the selected cell to fill cells `B2` to `B20` with the values of ln(C) corresponding to the values of C in column `A`.

Before calculating values of the electrode potential, we will first make life a little easier by setting up a table of constants, and pre-calculating the value of the slope term, RT/nF. In order to avoid problems, we will use absolute referencing when using these values.

1. Create a table of the following constants at a convenient place within the worksheet, such as cells `E1` to `F6`: E°(Cu2+) = 0.337 V, R = 8.31451 J mol-1 K-1, T = 298.15 K, n = 2, F=96485 C mol-1.
2. Create an entry for the term RT/nF, and enter the appropriate equation in the adjacent cell. Remember to use brackets to keep the terms in the denominator together!
3. Format the cells to show the correct number of significant figures and/or decimal places.
4. Enter the text `E (V)` in cell `C1`, create an equation for E in cell `C2`, and fill the cells down to complete the table of calculated electrode potentials.

Remember that ln(x) = loge(x)

Using COUNT, SUM, AVERAGE

You can now plot a graph of the electrode potential for a Cu|Cu2+ electrode as a function of (i) concentration and (ii) ln C. Don‘t forget to format the graph properly. You can use the options in the Scale tab of the Format Axis dialog to position the y-axis on the left-hand side of the graph.

As an additional exercise, change the formula to the more common "log" format by calculating 2.3026RT/nF, and changing column `B` to log C by using Excel's `LOG()` function.

Download a specimen Excel file for this exercise

Exercise 2: COUNT, SUM, and AVERAGE

Another very common calculation in analytical chemistry is the average of a series of values, (Σxi)/n. Excel has a built-in function to calculate the mean (`AVERAGE`), but it also includes the functions needed to set the calculation up as a formula. These are the `COUNT` and `SUM` functions.

1. In a new worksheet, create a column containing the values 10.01, 10.04, 10.02, 10.04, 10.03, and 10.05.
2. Beside this column, create a small table to contain the values of n, Σxi, and the mean.
3. In the cell beside the label for n, enter the text `=COUNT(` and click-drag to select the range of cells containing your values; type the closing paranthesis and press the return or enter key.
4. In the next cell, enter the text `=SUM(` and click-drag to select the range of cells containing your values; type the closing paranthesis and press the return or enter key.
5. In the next cell, explicitly calculate the mean by entering an equation referencing the cells created in the preceding steps (use relative cell referencing).
6. Confirm that your calculation gives the same result as the built-in `AVERAGE` function by using it in the same way as the `COUNT` and `SUM` functions.

In Excel, COUNT only counts cells containing numeric data. Use COUNTA to count cells containing alphanumeric data (e.g. names & numbers), and COUNTIF to count only specific items.

You can calculate the median and mode as well!