Investors, portfolio supervisors, and financial advisors use standard deviation as a fundamental risk metric. With an array of tools available in the market, businesses, researchers, and people seek for trustworthy tools to get insightful information and make wise decisions as they filter through massive databases. Over the years, Excel has become a popular and easy-to-use tool for conducting data analysis, and it doesn’t seem to lose its spark.
What is Standard Deviation?
The standard deviation is a measurement that represents the distribution of a data set with respect to its mean. By calculating the deviation of each data point from the mean, the standard deviation may be determined as the square root of variance. The standard deviation represents the "typical" variation from the mean. It is an effective metric of variability, considering it returns to the fundamental units of measurement of the data set.
Standard deviation could offer valuable insight into the concentration of data in a spreadsheet. A low standard deviation implies that all of the data points are closer to the average, whereas a high standard deviation suggests that the data points are scattered across a wider range.
You might often come across terms like "sample" and "population" in the context of standard deviation, which indicates the entirety of the data you are dealing with.
The following is the basic differentiating factor: Every component of data collection is included in the population. A sample is a segment of data comprising one or more components from the population. Researchers and analysts can use the formula for the standard deviation of a sample and population in different scenarios.
The basic formulas to calculate the standard deviation are
sample standard deviation (s)=(xi - x)2n - 1
population standard deviation ()=(xi - x)2n
Where
xi = individual values in the data set
x = mean of all x values
n = total number of x values in data set
How to Calculate Standard Deviation in Excel
The formula for standard deviation in Excel is a useful tool for analysts, researchers, students and professionals across diverse fields of study. There are six formulas for calculating the standard deviation in Excel. The formulas are based on the dataset, sample or population.
- STDEV.S, STDEVA, and STEDV are used to calculate the standard deviation in Excel for samples
- STDEV.P, STDEVPA, and STDEVP are used to calculate the standard deviation for an entire population
STDEV
It is an old formula which is available in the newer versions of Excel but only for backward compatibility only. In the 2007 version and later versions, the formula can have up to 255 arguments as numbers, arrays, named ranges or references to any cell which contains any numeric values.
Syntax for STDEV: STDEV(number 1, [number 2],....)
Where
number 1 is the first element of the dataset, you can use a named range, a single array or a reference to a single array.
number 2 is the optional argument in the formula, it refers to a data point, a named range, or a single array.
STDEV.S
The formula is used to calculate the standard deviation in numeric values, and it ignores the text and logical values.
Syntax for STDEV.S: STDEV.S(number 1, [number 2],....)
Where
number 1 is the first element of the dataset, you can use a named range, a single array or a reference to a single array.
number 2 is the optional argument in the formula, it refers to a data point, a named range, or a single array.
STDEVA
It is used to calculate the standard deviation in Excel for logical and text values. Either the data is typed into the list of arguments manually or stored in arrays, references, or both, all logical values are counted. All text values comprising empty strings (""), text forms of numbers, and other text, are counted as 0 inside arrays or reference variables. Text descriptions of integers provided directly in the argument list are considered as the numbers they indicate.
Syntax for STDEVA: STDEVA(value 1, [value 2],....)
STDEVP
It is an old formula in Excel which is used to calculate the standard deviation of a population. Like the STDEV.S, this formula only considers the numeric values and ignores the logical and text values. It is available in the newer version of Excel for only backward compatibility.
STDEV.P
It is a better and more accurate form of the STDEVP formula. It is used to calculate the standard deviation of the numeric values and also considers the references to the numeric value in a named range or single array.
STDEVPA
This formula works similarly to the STDEVA formula. It is used to calculate the standard deviation in the logical and text values in a data set. It does not include any numeric values.
Example
The formula for standard deviation in Excel is easy to use with a simple syntax with few to no errors possible. Let us consider the below example of a data set containing the scores of the students in a class.
The standard deviation of the entire population can be calculated using the formula STDEV.P.
To find the standard deviation in the scores of the students using the formula:
= STDEV.P (B2:B21)
The standard deviation of the score of a subset of students can be calculated using the formula:
= STDEV.S (B2:B13)
Alt text: Example of using standard deviation in Excel
We can notice a slight difference in the values of the standard deviation of the sample and the population. This kind of calculation can be used to see the performance of students in a class. A low standard deviation indicates that the scores are close to average and that the students understand the subject well.
Uses for Standard Deviation
Standard deviation is used in many disciplines for various reasons. Here are some of the prominent uses of standard deviation:
- A standard deviation is helpful in trading and investing methods since it helps gauge market and security variability and predict performance patterns.
- Standard deviation is widely employed by weather forecasters to describe the range of errors that the general public should expect in their predictions.
- The standard deviation is used to track and assess the variance of production processes in production and quality assurance. While a high standard deviation may demonstrate the need for process modifications, a low standard deviation implies that the process is trustworthy and consistent.
- Standard deviation is used to quantify the range of data points and evaluate the validity of results in scientific studies and experiments. It helps researchers determine whether the effects reported are significant or merely the result of chance.
- Standard deviation is used in the insurance sector to assess the cyclical nature of claim payouts and forecast prospective losses. It helps with insurance policy pricing and reserve allocation.
How to Add Standard Deviation Bars in Excel
Adding standard deviation bars is easy in Excel. Follow the following steps to add the standard deviation bars.
- Input your data in an Excel spreadsheet, then choose what range of data you would like to display in the chart.
- Select the “Insert” tab from the Excel menu.
- Select the “Charts” group's “Desired Chart Type” from the list that best illustrates your data, such as Column, Line, Scatter, etc.
- In the “Format” option, click the “Error bars” menu and choose the “Error bars with standard deviation” option.
Alt text: Adding standard deviation bars in Excel
Conclusion
The standard deviation is a key statistical measurement that is used in numerous disciplines, allowing researchers, analysts, and executives to make informed choices, detect patterns, and comprehend the variability in their data. The standard deviation formula in Excel is a crucial tool for data analysts, academics, and decision-makers in various industries. The postgraduate program in data analytics can help you learn about various tools and techniques for data analysis along with standard deviation.
FAQs
1. What is the difference between STDEV P and STDEV S?
STDEV P and STDEV S are the standard formulas to calculate the standard deviation in Excel. STDEV P is used to calculate the standard deviation in a given population, whereas STDEV S is used to calculate the standard deviation of the sample, which is a subset of the given population.
2. What is the formula for 2 standard deviations in Excel?
The formula for 2 standard deviations in Excel is given as =2STDEV(data range). The formula multiplies the standard deviation of the data set by 2, giving the value of 2 standard deviations.
3. What is the 3 STDEV rule?
According to the Empirical Rule, 99.7% of data that are found to have a normal distribution fall under 3 standard deviations of the mean. According to this formula, 68% of the data are under one standard deviation of the mean, 95% are inside two standard deviations, and 99.7% are within three standard deviations.
4. How much is 2 standard deviations?
The 2 standard deviations of the mean consist of about 95% of the values.
