How To Calculate Variance In Excel

7.1 min read|Last Updated: January 26th, 2024|Categories: excel|
table of content

The Excel statistical functions include the variance, the average (Internal link – how to calculate average in Excel), etc. There are about four hundred functions modified in Excel, and six of them calculate the variance exclusively.

Therefore the Microsoft office Excel software (MS-Excel) helps to reduce the computation time of this index.

In this tutorial, we will learn all these six functions and how to use them based on your data (The most crucial point is that you should know if your data is a sample or population.)

 

The Concept of the Variance

The evaluation of the spread among numbers in a data set is the Variance. Mathematically it’s specified as the average of the squared differences from the mean.

A large variance means numbers in the set are far from the mean and each other, while a small variance demonstrates the opposite.
Learn more: (https://www.mathsisfun.com/data/standard-deviation.html)

 

Calculating Variance in Excel

Excel variance formulas have two types of calculation:

  1. The Statistical functions calculate the variance for an entire population. These functions are VAR.P, VARP and VARPA.

VAR.P: 

This function calculates the variance of the whole population. By the way, the VAR.P function ignores cells with text and logical values (True or False) in reference.

The syntax of VAR.P function: 

=VAR.P(number1, [number2], …)

Number 1: The data (cell, number, etc.) that we want to calculate variance (you can enter the whole range instead of entering the data one by one.)

[number 2], [number 3], … are optional.

How to use the syntax of VAR.P function in Excel
Picture 1- The syntax of the VAR.P function

Use the INSERT FUNCTION Option to Calculate VAR.P

All functions are predefined in Excel. To enter the VAR.P formula, apply the following steps:

  1. Select an empty cell.
  2. Go to the Formulas tab from the ribbon.
  3. Click on the Insert Function from the Function Library group. 
  4. In the Insert Function dialogue box, you have two choices to enter the function.
  • Search for function (search VAR.P)
  • Select the category then select a function (select the Statistical category then select the VAR.P function from the menu.)
  1. Click OK, then the Function Arguments dialogue box appears.
  2. Click in the Number 1 box and enter the first data from the reference (number 1, number 2, … are 1 to 255 numeric arguments corresponding to a population.) Also you can enter your whole range in the Number 1 box.
  3. Press OK.
https://www.youtube.com/watch?v=sflM6lru2ak&list=PLyxa4neX6qJ0dVaKfhev8lmCT03mH-nCu&index=2&t=0s
Video 1- Use the Insert Function to calculate the VAR.P function

 

VARP: 

This function is compatible with Office 2016 and previous versions, but In the new versions, the VAR.P was replaced with the VARP. Anyway, there is no difference between these two functions.

The syntax of the VARP function:

=VARP(number1, [number2], …)

Number 1: The data (cell, number, etc.) that we want to calculate variance. (you can enter the whole range instead of entering the data one by one.)

[number 2], [number 3], … are optional.