Title: Calculating Division and Square Root in Excel
Course: BTEC-2000-301-302-303-F21 > Section: InnovaBio® Project Presentation

The formula for standard deviation asks you to find the sum (Σ) of the squares. You can use the same Excel “=sum” function that you used for the average. Now divide the sum of the squares by n-1.  The last step is to take the square root =SQRT (see the figure below).

The Excel sheet now has the calculations for the sum of the squares (1862.9, E15), the sum of the squares divided by n-1 (206.989, E16), and the square root (√ “=SQRT” )of the sum of the squares divided by n-1 (14.3871, E17). Remember to label your results so others can follow your work.

OK, so I took you through the whole formula to calculate standard deviation to help you become familiar with using the various Excel functions; absolute value (=ABS), dragging to use the same calculation in additional cells, square/exponent (^), and square root (=SQRT).

You probably guessed it, Excel has a function to determine the standard deviation of the data set (=STDEV).  You can see that the value from the previous set of calculations returns the same value as the Excel calculated standard deviation (see figure 5 below).

The standard deviation function in Excel (=STDEV) will save you time when you analyze your data. You can see that the result (B17=14.3871) is the same value as the long hand calculations (E17= 14.3871). Standard deviation is the default error value when calculating averages.

By calculating the Standard Deviation you now have the both parts of the result; an average value and the statistical significance.  If you were reporting your data in a presentation or publication you should only include the digits that are statistically significant.  In the tomato example with a standard deviation of 14 you can only be certain of the values to the tens digit.  Therefore, you should round the value to the tens digit (60) and report the results as “Average Mass of a Tomato is  60 + 14g.”

The degree of error is a major factor when considering the quality of the data.  In the example here, an error value nearly 25% of the reported result would not be viewed as very accurate or reliable. Hopefully the data you analyze in STUDENTfacturED® will have error values that are much smaller than these results.  In quality analysis for manufacturing, error values help you determine when the product is trending one direction or “out of control”.  See “Control Chart” in the next learning activity for more information.