Stats Tutorial - Instrumental Analysis and Calibration

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 Mⁿ⁺ + 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.

You can now plot a graph of the electrode potential for a Cu|Cu²⁺ 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.

Exercise 2: COUNT, SUM, and AVERAGE

Another very common calculation in analytical chemistry is the average of a series of values, (Σx_i)/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, Σx_i, 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.

Continue to Calibration & Trendlines...