Question

What is the difference between VAR.S function and VAR.P function on Microsoft Excel?

Recently, I answered a question posted by @Yiu A.
https://socratic.org/questions/how-to-do-this-regression-question

In the question, I tried to calculate the variance of the following `15` samples.
`42,34,25,35,37,38,31,33,19,29,38,28,29,36,18`

The result obtained by Microsoft Excel was
VAR.P(`42,34,25,35,37,38,31,33,19,29,38,28,29,36,18`)=44.78222 and
VAR.S(`42,34,25,35,37,38,31,33,19,29,38,28,29,36,18`)=47.98095.

I thought that the function VAR.P gives us the population variance and VAR.S gives us the sample variance.
If so, the population variance should be larger than the sample variance, but the result is quite the opposite.

I would like to know how to interpret the result. Thank you in advance.

Answer 1

VAR.S > VAR.P

Explanation:

VAR.S calculates the variance assuming given data is a sample.

VAR.P calculates the variance assuming that given data is a population.

VAR.S `= \frac{\sum(x - \bar{x})^2}{n-1}`

VAR.P `= \frac{\sum(x - \bar{x})^2}{N}`

Since you are using the same data for both, VAR.S will give a value higher than VAR.P, always.

But you should use VAR.S because the given data is in fact sample data.

Edit: Why the two formulas differ? Check out Bessel's Correction.