How to calculate variance in Excel

Variance is a measure of distance between numbers in a data set. Its measures the distance difference each number is from the mean. This distance is also known as the error term in which the variance is measured. It's very easy to calculate variance in Excel if you have all the data set already entered into the software. Variance is calculated by a sample of data taken from a large population. There are two types of formulas used and they have different functions in Excel.

Functions used to calculate Variance

  • Function with p

It gives the standard deviation for the actual value you have entered.

They assume the data you have is the whole population.

    =VAR.P (A1:A7)

 

  • Functions with S

It gives the standard deviation for a whole population where the sample data is taken.

This formula provides the estimated variance for the population.

S indicates the dataset is a sample, but the results are for the population.

   =VAR.S (A1:A7)

 

Steps on how to calculate variance in Excel;

1. Open your Excel worksheet installed on your pc with data.

2. You must ensure the data you have entered is in a single range of cells in Excel.

 

3. If the data you have represents the entire population, you will use this formula VAR.P (A1:A20). On the other hand, if the data you have is a sample for some larger population, you will this formula VAR.S (A1:A20).

 

4. Here your variance for the data will be displayed in the cell.

Standard Deviation

Calculating a standard deviation is similar to calculating variance in excel. They both have similar properties and characteristics.

STDEV and STDEV.S Functions in Excel

The STDEV function and STDEV.S functions are tools that help one to estimate the standard deviation based on the set of data.STDEV.S function is mostly used nowadays. STDEV is used as a compatibility function thus it can be used to ensure backward compatibility. The STDEV and STDEV.S functions provide an estimated dataset of a standard deviation. The dataset entered represents only a small sample of the total population and as result, it does not return the exact standard deviation. Both of the functions have identical behaviors

 

STDEV and STDEV.S Syntax and Arguments

STDEV (number1, [number2], …)

Number 1 is a must that it is required. It can be a named range, actual number, or cell references of the data in an Excel worksheet. If cell references are used, then the rest in the range of cell references are ignored. Number 2 is optional.

STDEV.S (number1, [number2], …)

Number 1 is mandatory that it is required. Single arrays can also be used instead of arguments. Number 2 is optional.

 

Steps on how to calculate standard deviation in Excel;

1. You must enter all the data you require in MS Excel.

The data you entered must be in Excel range that is a column, a row, or a group of a matrix of columns and rows before using the statistics function in excel.

 

2. Select all the data without selecting any other values.

This is because we need that data only not any other value.

3. If the data represents the entire population use the following formula STDEV.P (A1:A20).

 

On the other hand, if the data represents a sample from some large population use the following formula STDUEV (A1:A20).

 

4. The standard deviation for the data will be displayed in the cell.