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\
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
From above table, ∑ (x x̄)^2 = 490
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
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̄

(xx̄)

(xx̄)

(xx̄)^2

(xx̄)^2

170

174

=170174

4

=4^2

16

183

174

=183174

9

=9^2

81

183

174

=183174

9

=9^2

81

165

174

=165174

9

=9^2

81

172

174

=172174

2

=2^2

4

177

174

=177174

3

=3^2

9

181

174

=181174

7

=7^2

49

161

174

=161174

13

=13^2

169

Total

490

From above table, ∑ (x x̄)^2 = 490
Therefore Variance for the given sample is 490/(81) = 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.
Hi Saran,
ReplyDeleteCongratulations on having a great blog, all posts are very informative and explained very well. I like the layout very much. I hope the number of viewers to this blog will increase faster and reach to a huge count. Keep up good work.
Thanks for your wishes, Ahsan
Delete