# How to Calculate Variance In Excel | VAR.S & VAR.P

By gobrain

Jun 9th, 2024

Variance is a statistical tool used to evaluate the dispersion of data in a set. It gives information about how much the individual data points differ from the mean of the set.

Imagine you have a bunch of numbers, like test scores for a class. Variance tells you how spread out those scores are. Are they all bunched up close to the average score, or are they scattered far apart?

- Low variance: Scores are clustered tightly around the average. Everyone did pretty similar.
- High variance: Scores are all over the place. Some students did much better or worse than average.

And, Excel is a widely used software for analyzing datasets, making it important to know how to calculate variance in a spreadsheet. Let's examine the formula for variance and then how to find it on Excel.

## What is the Formula for Calculating Variance?

The formula for calculating variance in a dataset is as follows:

```
V = (Σ(x - μ)^2 / n)
```

Where:

- x is a data in set,
- μ is the mean,
- n is the number of values in set
- Σ is the sum of.

### Population vs Sample in Variance

Due to the difficulty in obtaining data for the entire population, a sample is taken from the population and the variance is calculated. As a result, there will be a slight modification in the formula, as follows:

**V = (Σ(x - μ)^2 / (n-1))**

## How To Calculate Variance In Excel

In Microsoft Excel, you can use the `VAR.S`

function to calculate the sample variance of a dataset. The formula is as follows:

```
=VAR.S(range)
```

Where range represents the group of values for which you want to determine the variance. For example, if your data is located in the range `A1:A10`

, the formula to calculate the variance would be:

```
=VAR.S(A1:A10)
```

If you want to calculate the variance of the population, the formula is:

```
=VAR.P(range)
```

And, data located in the range `A1:A10`

, the formula to calculate the variance of the population would be:

```
=VAR.P(A1:A10)
```

## Conclusion

In this brief guide, we have demonstrated the formulas for population and sample variance and how to calculate them using Microsoft Excel, a widely used spreadsheet for data analysis

Thank you for reading.