What is standard deviation?
Standard Deviation in Excel estimates the standard deviation based on a sample or population. Standard Deviation/SD is a measure of the range or width with which values are dispersed from the average mean value.
Standard Deviation in Excel
Bear in mind that this function has been replaced with better or newer functions that may provide better user accuracy. Though the SD function is still there for backward compatibility, try using future versions of the function like STDEV.S.
The STDEV function involves the use of the following syntax:
STDEV ( number 1, [ number 2 ],….)
The STDEV function syntax covers the following arguments:
Number 1: This is the required number. It is the first number corresponding to the population’s sample.
Number 2: This is an optional number. Number arguments range across 2 to 255 corresponding to the population’s sample. Users can opt for a single array or reference to an array rather than arguments separated by commas as well.
Note that STDEV assumes the arguments are a sample of the population. For data representing the entire population, it is essential to use STDEVP to compute standard deviation.
Excel calculates STDEV using the n-1 method. Arguments can range across references containing numbers, arrays, numbers or names. Additionally, logical values and text representations of numbers typed directly into the argument lists are counted. For an argument which is a reference or an array, numbers in the array/reference are only counted. Error values, logical values, text or bare cells in the array of references are left out.
Arguments containing error values or text cannot be translated into numbers and this can cause errors. If text representations of numbers and logical values are needed as part of the calculation, use STDEVA function.
The following formula is used for STDEV:
x= Sample mean average (number 1, number 2, number 3…)
n= Sample size
With the launch of Excel 2010, the two existing SD functions were renamed and updated. But the older functions are still available in Excel’s current version, to be compatible with previous versions.
Formula for Population SD is given below:
x- each value in the set
x̄ = Average population mean
Σ = Sum of
n= number of items in sample size/values in set
For the sample, SD can be computed using:
Here, x̄ is the sample average mean.
How to calculate standard deviation in Excel
For calculating SD, the STDEV function is the oldest one, in Excel available in all versions from 2003 to 2016. STDEV can take up to 255 arguments and representation includes arrays, numbers, named ranges or references to cells containing digits.
STDEVS is an improved version introduced in Excel 2010. This calculates the sample SD based on sets of values. For either of the above functions to work effectively, the supplied arguments must have a basic of two numeric values otherwise error results.
If you want an Excel SD function that handles logical and text values, use STDEVA. Here all logical values are counted. Text values within array/reference are counted as 0 including empty strings, a text representation of digits and other text. However empty cells are ignored.
STDEVP is an old Excel function to find the SD of the population. In the newer versions of 2010, 2013 as well as 2016, it is replaced with STDEV.P. Both STDEVP plus STDEV.P count numbers only apart from also counting logical values and textual representations of numbers.
STDEVPA on the other hand, calculates the SD of the population, including text and logical value. For nonnumeric values, it functions exactly like STDEVA.
Steps to Calculate Excel Standard Deviation
#1 Keep the data list ready
Maintain the data list to a single variable. SD is generally on a small sample or the wider population. Excel offers different formulas, so choose as per your requirement.
#2 Open Your Microsoft Excel Program
The function and location of the Excel program differ depending on versions used. The function can be found in a button on the formatting bar or the top horizontal tool-bar.
#3 Enter the Data
Make a chart of your data, enter headings in columns and name or date identifying each occurrence on the row’s left side, entering the corresponding information or data to the right. Also, save your excel document to avoid losing data.
#4 Calculate SD
Place your cursor below the last number in the box. Place the standard deviation number in any empty cell in the Excel spreadsheet. Excel can automatically compute data range if it is placed beneath the list of numbers.
#5 Enter the Equals Sign
Enter the formula without spaces and the equals sign. Type in STDEV, the Excel syntax for SD. When this abbreviation is used for the data range, Excel gets the mean and SD automatically.
Remember to use STDEVA or STDEVAP depending on whether you need a different type of formula. Choose STDEVP or STDEVS depending on whether you want to the computer using the entire or just a sample population. Insert the data range in the following way (C2:C15). Data range refers to series of numbers in columns or rows. Next, press enter and the sample standard deviation should appear In the box.
Note: You can also use Excel’s formula wizard to choose the SD formula. Click on Insert in the Formula bar, choose the Statistical category and select STDEV. Then type the data range in the box popping up. Press OK to complete your formula.
Benefits of standard deviation
STDEV function in Excel offers a lot of benefits. It indicates how values of sets of data disperse or deviate from the mean. It also shows whether data is close to the mean or fluctuates a lot, which is beneficial for companies looking to understand monthly production as compared to average production in a year for example. Standard Deviation also shows if the mean returns typical types of data. The closer the SD is to 0, the more reliable the mean is as data variability is low and vice versa.
