The standard deviation is a measure of how far away from the mean a set of numbers are.
What is standard deviation and its use:
A statistic called standard deviation assesses a dataset’s dispersion in relation to its mean. It is determined as the variance’s square root (the spread of numbers in a dataset). When comparing data sets that may have the same mean but a distinct range, knowing the variation between each data point relative to the mean is helpful.
- For example, the numbers below have a mean (average) of 20 and a standard deviation is 0 as outlined in Red
Explanation: Since all of the numbers are the same, there is no variation. The numbers therefore have a zero standard deviation. The STDEV function has been around for a while. Microsoft Excel advises utilizing the brand-new STEDV.S function, which yields the same outcome.
1.The numbers below also have a mean (average) of 20, but standard deviation is 1.
Explanation: The data are reasonably near to the mean. The numbers have a small standard deviation as a result.
2. The numbers below also have a mean (average) of 20 but standard deviation is 8.774964 as outlined in Red
Explanation: The numbers are dispersed. The numbers have a significant standard deviation as a result.
Uses of Standard Deviation:
Because it may be used to gauge market volatility, standard deviation is often utilized to plan trading and investing strategies. Standard deviation is used by analysts, portfolio managers, and advisors as a fundamental risk indicator. Even the standard deviation of their mutual funds will be disclosed by investment firms.
The statistics are often simple to comprehend, which makes it useful to present them to clients and investors. Excel may be quite helpful in terms of time management by calculating and displaying the standard deviation.
Calculating Standard Deviation in Excel:
The standard deviation is simple to compute, but you must be aware of the Excel formula to use.
In Excel, there are six standard deviation formulas, outlined in Blue below.
Two groups can be made up of these six formulas:
The formulas in this category are STDEV.S, STDEVA, and STDEV for calculating the sample standard deviation.
Calculating the population’s overall standard deviation: This category includes the formulas STDEV.P, STDEVPA, and STDEVP.
Let’s now comprehend these six formulas:
STDEV.S- When your information is numerical, use this. The text and logical values are disregarded.
STDEVA – When you want to include logical and text values in the calculation, use this (along with numbers). Text and FALSE are interpreted as 0, and TRUE as 1.
STDEV – Excel 2010 introduced STDEV.S. The STDEV function was utilized earlier. For backward compatibility with earlier versions, it is still present.
STDEV.P – The standard deviation for a sample set of data is calculated using the Excel STDEV.P function. STDEV.P uses the “n” method to calculate standard deviation while ignoring logical values and text.
STDEVPA – The Excel STDEVPA function determines the population-wide standard deviation. STDEVPA examines text and logical that appear in references, as contrast to the STDEVP function. Use STDEVA or STDEV.S to determine a sample’s standard deviation.
STDEVP – For data that represents a population, the STDEVP function determines the standard deviation. A more recent function called STDEV.P has taken the place of STDEVP and exhibits the same behavior.
Using STDEV.S Function in Excel:
Excel’s STDEV.S function, where S stands for sample, calculates the standard deviation using a sample. Take the situation of instructing a sizable class of students. Only five pupils’ test results are available. There are 5 samples total. The formula for the STDEV.S function is as follows:
In this example, x1 = 10, x2 = 8, x3 = 9, x4 = 5, x5 = 3, x6 =2, x7 =7, x8 =9, x9 =10, x10 =6, x̄ = 6.9 (Sample mean), n = 10 (Number of data points).
Step 1: Calculate the mean (μ). Result is outlined in Red below.
Step 2: Determine the distance to the mean for each number. Result and formula are outlined in Red below.
Step 3: Calculate the square of each number. Result and formula are outlined in Red below.
Step 4: Sum the squared numbers. Result and formula are outlined in Red below.
Step 5: Divide by n-1 (n=Number of data points). Result and formula are outlined in Red below.
Step 6: Square root the number. Result and formula are outlined in Red below.
Step 7: Fortunately, Excel’s STDEV.S function can carry out each of these actions for you. Result and formula are outlined in Red below.
Note: When estimating the standard deviation based on a sample, why do we divide by n – 1 instead than by n? According to Bessel’s correction, multiplying by n-1 as opposed to n results in a more accurate assessment of the standard deviation.
Using STDEV.P Function in Excel:
The STDEV.P function in Excel determines the standard deviation based on the entire population (the P stands for Population). Let’s say you’re selling 10 products. You have each product’s price. There are 10 data points in the population as a whole. The formula used by the STDEV.P function is as follows:
In this example, x1 = 10, x2 = 8, x3 = 9, x4 = 5, x5 = 3, x6 =2, x7 =7, x8 =9, x9 =10, x10 =6, μ = 6.9 (mean), N = 10 (Number of data points).
Step 1: Calculate the mean (μ). Result is outlined in Red below.
Step 2: Determine the distance to the mean for each number. Result and formula are outlined in Red below.
Step 3: Calculate the square of each number. Result and formula are outlined in Red below.
Step 4: Sum the squared numbers. Result and formula are outlined in Red below.
Step 5: Divide by the number of data points (n = 10). Result and formula are outlined in Red below.
Step 6: Square root the number. Result and formula are outlined in Red below.
Step 7: Fortunately, Excel’s STDEV.P function can carry out each of these actions for you. Result and formula are outlined in Red below.
Elaborate VARINACE (VAR.S and VAR.P) function:
A dataset’s variability can be estimated by looking at its variance, which is a number. There is no variability when the variance is 0, meaning that each and every number in the dataset is the same. The values are more erratic as the number gets larger. The variance reveals how widely the dataset’s individual participants deviate from the mean.
Calculations of variance can be divided into two categories: sample variance and population variance.
- Population variance denotes the presence of each individual in the population in the dataset.
- Sample variance denotes the fact that a sample of the population was used to get the data.
VAR.S Function: Based on a sample, the VAR.S function below calculates the variance. Result and formula are outlined in Red below.
VAR.P Function: The variance is calculated using the entire population through the VAR.P function below. Result and formula are outlined in Red below.
Note: Applying the new VAR.S function is suggested in Microsoft Excel.
For ready-to-use Dashboard Templates:
