How to Calculate Mean Variance and Standard Deviation in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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,

calculate mean variance and standard deviation in excel

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.


How to Calculate Mean Variance and Standard Deviation in Excel: 3 Easy Steps

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.

calculate mean variance and standard deviation in excel


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.

Calculate Mean and Variance in Excel

  • 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.

Calculate Mean and Variance in Excel

  • 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.

Calculate Mean and Variance in Excel

Read More: How to Apply Variance Formula in Excel to Get Plus-Minus Results


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.

Calculate Mean and Variance in Excel

Read More: How to Calculate Sample Variance in Excel


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)

Calculate Standard Deviation in Excel

  • Further, press ENTER on your keyboard, and you will get 8.896940798 as the return of the STDEV function.

Calculate Standard Deviation in Excel

Read More: How to Find Population Variance 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).

calculate mean variance and standard deviation in excel

Read More: How to Find the Variance of a Probability Distribution 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.

calculate mean variance and standard deviation in excel


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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo