In this section, we consider two very useful Excel functions that
you will use to develop calibration curves. Excel functions are
built-in formulas that perform frequent operations. The two functions
we will review here are SUM and AVERAGE.
Functions can be entered in cells as part of an equation, which begins
with an "=" sign.
You will enter fluorescence intensities and use this data to generate a calibration curve related concentrations in pg·ml-1 to the intensities, from Miller and Miller. This data will then be used in all the subsequent examples. Enter the following data in the second two columns of an Excel spreadsheet. Column B should contain the fluorescence intensities and column C the concentrations.
| Fluorescence Intensities |
Concentration (pg/ml) |
|---|---|
| 2.1 | 0 |
| 5.0 | 2 |
| 9.0 | 4 |
| 12.6 | 6 |
| 17.3 | 8 |
| 21.0 | 10 |
| 24.7 | 12 |
The SUM Function
The SUM function in Excel is used to add all the
elements in a series of data. This is fundamental for statistical
analysis, since all applications involve the sum of a series of numbers
or samples. Its use is straightforward when operating on a series of
data, such as the fluorescence intensities in the previous exercise.
Using this data as a starting point, we will see how the SUM
function works.
- If it is not already open, find and open the fluorescence data from the previous exercise.
- In the cell directly under the concentration values (should be
cell B9, from the previous exercise), type
=sum(then highlight the cells you want to sum (in this case, B2-B8). The formula in the cell should now read=sum(B2:B8. Close the bracket and press Enter. The value in the cell should now be 42. This should come as no surprise. - Do the same for the fluorescence intensity data. The sum should be 91.7.
Throughout this tutorial, whenever you see the symbol Σ in a formula, it indicates summation over a series of samples or data.
The AVERAGE Function
The AVERAGE function, which calculates the mean of a
set of samples, is also very useful in statistical analysis. A full
definition of the arithmetic mean of a set of data is quite complicated
and involved. A simple definition is that the mean is the expected
result of any process. It is important to not confuse the population
mean with the sample mean. The sample mean is the mean for a set of
discrete samples n, given by the formula (Σxi)/n,
where the xi are all the discrete samples. The
average of a set is denoted by the symbol xbar, so any formula
containing this indicates the use of the AVERAGE
function.
The population mean is the expected value for a process as n approaches infinity. For instance, if we take a very large number of samples (normally on the order of 106 or even higher), we might approach the population mean. It is the true expected value, and is normally denoted μ, and the sample mean is an approximation it. However, the population is not used extensively in this tutorial and is only presented here as to make you aware of the difference.
The AVERAGE function is used in the same way in Excel
as the SUM function, except the word sum is replaced with
average. To calculate the average fluorescence intensity, the cell
where you wish to do this should look like =average(A2:A9).
The calculated value should be 13.1.
You are now ready to start calculating calibration curves using Excel. An introduction to Calibration Curves is presented on the next page, and theory of Regression Analysis is covered in a later section.