Excel VAR Formula - Statistical Formula for 'Sample Variance'

Today we all are going back to our school days, and learning some formulas in Statistics. Mean, Mode, Median, Variance, Standard Deviation etc., are words, which we use in the subject Statistics.

One of the Statistical concept is Variance. There is a formula in MS Excel for calculating Variance for a sample of data, that is VAR.

Before going into the excel formula VAR, let me explain what is Variance and what is the statistical formula for Variance.

What is Variance?

Statistical Variance gives a measure of how the data distributes itself about the mean or expected value. Unlike range that only looks at the extremes, the variance looks at all the data points and then determines their distribution.


In simple words, Variance is a measure of how far a set of numbers is spread out. In other words, Variance is describing how far the numbers lie from Mean (Average).

If we observe the above charts, from the mean point first chart data points are closer than second chart data points. So, we can say that Variance is more for the second chart data points.

Statistical Formula to calculate Variance?

The statistical formula for sample Variance is 

Where as, 
x=sample data
x̄=Mean of the sample data (average)
n=Number of sample data points\

∑=Sum

Solve the Variance for the sample data?

The below are the heights of male in India between the age 25 to 30 years (in cms). 

Sample data picked up from population :  170,183,183,165,172,177,181 and 161

Mean = x̄ = Average(170,183,183,165,172,177,181,161) = (170+183+183+165+172+177+181+161)/8 = 174 cms

Height (cms)	Mean			Mean^2	Mean^2
x	x̄	(x-x̄)	(x-x̄)	(x-x̄)^2	(x-x̄)^2
170	174	=170-174	-4	=-4^2	16
183	174	=183-174	9	=9^2	81
183	174	=183-174	9	=9^2	81
165	174	=165-174	-9	=-9^2	81
172	174	=172-174	-2	=-2^2	4
177	174	=177-174	3	=3^2	9
181	174	=181-174	7	=7^2	49
161	174	=161-174	-13	=-13^2	169
				Total	490

From above table, ∑ (x- x̄)^2 = 490

Therefore Variance for the given sample is 490/(8-1) = 490/7 = 70 cms

Excel VAR formula?

Now, my work is very easy. If you understand the all above concept for Variance that is nothing but VAR formula.

To find out Variance, we did so many calculations. But MS Excel calculates Variance with a simple formula VAR.

Syntax of VAR formula: VAR(number1,number2,...)

Number1, number2, ... are 1 to 255 number arguments corresponding to a sample of a population.

Therefore, Variance for the above sample data is 

=VAR(170,183,183,165,172,177,181,161) = 70 cms

Points to be remember for VAR formula? 

(this part has been taken from MS Excel help)

• Formula VAR assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the variance by using Formula VARP. 
• Arguments can either be numbers or names, arrays, or references that contain numbers. 
• Logical values, and text representations of numbers that you type directly into the list of arguments are counted. 
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. 
• Arguments that are error values or text that cannot be translated into numbers cause errors. 
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the VARA function.

Finance professionals may not use this function regularly, but this function may be useful for the statistical professions and professionals who are working with surveys etc.,

Get Free Updates:
*Check your email to confirmed your subscription*