Title: Calculating Standard Deviation in Excel
Course: BTEC-2000-301-302-303-F21 > Section: InnovaBio® Project Presentation

Finding the average of your data is only half of the result.  The other half is to determine the statistical significance.  If your data is not consistent from experiment to experiment it is difficult to determine if your hypothesis or study plan is supported or refuted by your data.  When you are calculating averages the most common statistical analysis is Standard Deviation.  The calculation determines how much different each data point is from the average or the mean.  There are two possible formulas (see below). In some cases it is statistically relevant to choose either equation A or B.  We will proceed with the calculation in formula B. In the formula is below, SD, S and 𝛔 ( lower case sigma) are all symbols meaning standard deviation.  At the heart of the equation is the calculation of each data point minus the average (xi-xave).  The vertical lines on either side of the x values indicate absolute value. The calculated absolute value is then squared. The Σ (capital sigma) indicates that you add up the sum of the squares then divide by n (# of data points) or n-1.  The symbol (√) over the whole formula and indicates that you take the square root to find the standard deviation.

Definitions:

Absolute Value: The value of the number regardless of whether it is positive or negative.

Square or Square Number: The product of multiplying a number by itself.

Square Root: The value of the number when multiplied by itself equals the number (under the √).

We will use Excel to do these calculations for our original data set of Roma tomatoes.  To instruct Excel to only give the result for absolute value use the text =ABS.  Use Excel to find |xi-xave| for the first data point by typing =abs(B5-B16). B5 has the first data point and B16 has the calculated average.

To calculate absolute value in Exel use the syntax =ABS. As you write the equation a parenthesis will appear. Write the corresponding cells and function you would like Excel to perform ex. (xi-xave).  Typing the right parenthesis tells Excel all the elements of the calculation are included. Now hit the Enter key. You can see the correct syntax in the box above column C and the result in the selected cell.

Now you can easily modify the equation to tell Excel to repeat the calculation for the remaining data points. The average value is in a fixed location (B16) relative to all the other data points. The dollar sign in Excel indicates that the following reference symbol should not vary as you drag the formula over many cells.  Here is the Excel formula =ABS(B6-B$16).  To apply the formula to the remaining data points, select the cell and hover over the lower right corner until you see the solid black plus sign and drag down to the last data point.  In Figure 2 you can see the last calculation references the data points in B14 and the average in B16.  Now you have the absolute value for the difference between the average and each data point.

The Excel image has Column D labeled |xi-xave|to indicate each calculated value is the absolute value of the difference between the data point and the average.  The syntax for the tenth data point (B14) is written above Column C.  A dollar sign to the left of 16 instructs Excel to use that cell (B16) for each equation when you drag one equation to additional cells.