Md. Abdur Rahim Rasel
The variance, standard deviation and mean deviation are closely related to each other. While working with large Microsoft Excel, now and then, we have to calculate the mean variance and standard deviation. Calculating Standard errors in Excel is an easy task. In statistics, standard deviation calculates a group of data by which you measure how the calculation is spread out from an expected or average value. It is defined as the square root of the dispersion or variance in a frequency distribution. Today, in this article, we’ll learn three quick and suitable steps to calculate mean variance and standard deviation in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to Mean Variance and Standard Deviation
In this portion, I will introduce to you the Mean, Variance, and standard deviation.
Mean
The average of a set of data is called the mean. The formula of Mean in Excel is,
Mean = =AVERAGE(data_range)
Variance
The dispersion between numbers in a data collection is measured by variance. The variance represents the deviation of each integer in the set from the mean. The formula for calculating the variance is,
Where
- μ is the arithmetic mean.
- X is the individual value.
- N is the size of the sample.
- σ is the variance.
Standard Deviation
The standard deviation of a dataset is a statistic that indicates its dispersion in relation to its mean. The standard deviation means the square root of the variance. The formula for calculating the standard deviation is,
Standard Deviation = Sqrt (Variance)
When comparing sets of data with the same mean but distinct ranges, determining the variation between each data point relative to the mean is useful. In finance, the standard deviation is frequently used. It’s used to calculate an investment’s yearly rate of return. The bigger the disparity between each price and the mean, the larger the price range is revealed by a higher standard deviation. The standard deviation of a volatile stock is high, but the standard deviation of a blue-chip stock is low.
3 Easy Steps to Calculate Mean Variance and Standard Deviation in Excel
Let’s assume we have an Excel large worksheet that contains the information about several students of Armani School. The name of the students, the Identification Number, and the securing marks in Electrical and Electronics Engineering(EEE) are given in Columns B, C, and D respectively. We can easily calculate the Mean, Variance, and Standard Deviation in Excel by using COUNTA, AVERAGE, VAR, STDEV, SQRT Functions, and so on. Here’s an overview of the dataset for today’s task.
Step 1: Calculate Mean Value in Excel
To calculate the standard deviation, firstly, we will calculate the mean and variance. From our dataset, we can easily calculate the mean and variance. Let’s follow the instructions below to calculate the mean and variance!
- First of all, select a cell. We will select cell D15 for the convenience of our work.
- After selecting cell D15, write down the COUNTA function in that cell. The COUNTA function is,
=COUNTA(D5:D14)
- Hence, simply press ENTER on your keyboard. You will get 10 as the return of the COUNTA function which is the sample size.
- After calculating the sample size, we will calculate the mean of the marks secured in the EEE subject by the students. Write down the below formula in cell D16.
=AVERAGE(D5:D14)
- Again, press ENTER on your keyboard, and you will get 72.6 as the return of the AVERAGE function which is the Mean of our dataset.
Read More: How to Calculate/Find Mean and Standard Deviation in Excel
Similar Readings
- How to Calculate Portfolio Variance in Excel (3 Smart Approaches)
- Calculate Standard Deviation of a Frequency Distribution in Excel
- How to Create a Bell Curve with Mean and Standard Deviation in Excel
Step 2: Use VAR Function to Calculate Variance
- Further, we will calculate the variance of our dataset. To calculate the variance, Write down the VAR function in cell D17.
=VAR(D5:D14)
- After that, press ENTER on your keyboard. As a result, you will get 79.15555556 as the return of the VAR function which is the Variance of our dataset.
Read More: How to Calculate Sample Variance in Excel (2 Effective Approaches)
Step 3: Apply STEDV Function to Calculate Standard Deviation in Excel
- Now, we will calculate the standard deviation by using the STDEV Type the STDEV function in cell D18.
=STDEV(D5:D14)
- Further, press ENTER on your keyboard, and you will get 8.896940798 as the return of the STDEV function.
Read More: How to Calculate Average and Standard Deviation in Excel
Calculate Standard Error in Excel
Meanwhile, we will calculate the standard error by using the standard deviation. Let’s follow the instructions below to calculate the standard error!
- Firstly, select cell D18. Then write down the below formula in that cell. The formula is,
=D18/SQRT(D15)- Where D18 is the standard deviation, and D15 is the sample size.
- After typing the formula, simply press ENTER on your keyboard. You will get 2.813459713 as the standard error. As our standard error is greater than 2, we will calculate the Standard Error of Skewness(SES).
Read More: Generate Random Number with Mean and Standard Deviation in Excel
Calculate Standard Error of Skewness in Excel
Last but not the least, in this step, we will calculate the standard error of skewness as our standard error is 2.521904043 which is greater than 2. Let’s follow the instructions below to calculate the standard error of skewness!
- To calculate the standard error of skewness, select cell D20 and type the SQRT function in that cell. The SQRT function is,
=SQRT((6*D15*(D15-1))/((D15-1)*(D15+1)*(D15+3)))
- Further, press ENTER on your keyboard, and you will be able to calculate the standard error of skewness. The standard error of skewness is 647750276 which has been given in the below screenshot.
Things to Remember
👉 There are three types of formulas to calculate the standard deviation. Such as 1. STDEV.S(for sample), 2. STDEV.P(for population), and 3. The STDEV function is an old function.
👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
👉 In Microsoft 365, Excel will show the #Value! Error if you don’t select the proper dimension. The #Value! error occurs when any of the elements of the matrices is not a number.
Conclusion
I hope all of the suitable methods mentioned above to calculate mean, variance, and standard deviation will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- How to Calculate Budget Variance in Excel (with Quick Steps)
- Budget vs Actual Variance Formula in Excel (with Example)
- How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)
- Calculate Percentage Variance between Two Numbers in Excel
- How to Calculate Pooled Variance in Excel (with Easy Steps)
- Calculate Coefficient of Variance in Excel (3 Methods)
- How to Calculate Variance Percentage in Excel (3 Easy Methods)
