VAR

 The VAR function in Excel is used to calculate the variance based on a sample of data. Variance measures how far a set of numbers are spread out from their average value. Below are 10 examples of how you can use the VAR function, including data and formulas.

Example 1: Basic Variance Calculation

Data:

Copy

A1: 10
A2: 20
A3: 30
A4: 40
A5: 50

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 10, 20, 30, 40, and 50 is 250.

Example 2: Variance with Negative Numbers

Data:

Copy

A1: -5
A2: 0
A3: 5
A4: 10
A5: 15

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers -5, 0, 5, 10, and 15 is 62.5.

Example 3: Variance with Decimal Numbers

Data:

Copy

A1: 1.5
A2: 2.5
A3: 3.5
A4: 4.5
A5: 5.5

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 1.5, 2.5, 3.5, 4.5, and 5.5 is 2.5.

Example 4: Variance with Text and Numbers (Text Ignored)

Data:

Copy

A1: 10
A2: 20
A3: "Text"
A4: 40
A5: 50

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 10, 20, 40, and 50 is 291.6667.

Example 5: Variance with Logical Values (TRUE/FALSE Ignored)

Data:

Copy

A1: 10
A2: 20
A3: TRUE
A4: 40
A5: FALSE

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 10, 20, and 40 is 233.3333.

Example 6: Variance with Empty Cells (Empty Cells Ignored)

Data:

Copy

A1: 10
A2: 
A3: 30
A4: 
A5: 50

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 10, 30, and 50 is 400.

Example 7: Variance with a Single Number

Data:

Copy

A1: 100

Formula:

excel

Copy

=VAR(A1)

Result:
The variance of a single number is #DIV/0! because variance cannot be calculated with only one value.

Example 8: Variance with a Range Including Zero

Data:

Copy

A1: 0
A2: 0
A3: 0
A4: 0
A5: 0

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 0, 0, 0, 0, and 0 is 0.

Example 9: Variance with a Large Range of Numbers

Data:

Copy

A1: 1000
A2: 2000
A3: 3000
A4: 4000
A5: 5000

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 1000, 2000, 3000, 4000, and 5000 is 2,500,000.

Example 10: Variance with a Mixed Range (Numbers, Text, Logical Values)

Data:

Copy

A1: 10
A2: "Text"
A3: 30
A4: TRUE
A5: 50

Formula:

excel

Copy

=VAR(A1:A5)

Result:
The variance of the numbers 10, 30, and 50 is 400.

Summary

The VAR function in Excel is a powerful tool for calculating the variance of a sample dataset. It automatically ignores text, logical values, and empty cells, making it versatile for various types of data. Remember that variance is a measure of dispersion, and it gives you an idea of how spread out your data points are around the mean.

If you need to calculate the variance for an entire population, you should use the VAR.P function instead.