# VAR Function in Excel

๐ Part 1: Introduction ๐

The VAR function in Microsoft Excel is used to calculate the variance of a dataset. Variance measures the dispersion of values in a dataset, indicating how spread out the data points are from the average value. The VAR function is handy in statistical analysis and risk assessment.

Syntax:

syntax
`VAR(number1, [number2], ...) `

Arguments:

• `number1`, `number2`, …: These are the numeric values or cell references representing the dataset for which you want to calculate the variance. You can include up to 255 arguments, and each argument can be a number, a cell reference, or a range of cells.

Return value: The VAR function returns the variance of the dataset.

Remarks:

• The VAR function calculates variance using the “sample” formula. If you want to calculate the population variance, use the VARP function instead.

Now let’s move on to some examples to understand how to use the VAR function effectively.

๐ Part 2: Examples ๐

Example 1:ย

A
110
215
320

๐ Formula: In cell B1, enter `=VAR(A1:A3)`

Explanation: In this example, we have a dataset with three values (10, 15, and 20). We calculate the variance of these numbers by using the VAR function with the range A1:A3. The result in cell B1 will be 16.6667.

Example 2:ย

AB
157
2310
386

๐ Formula: In cell C1, enter `=VAR(A1:B3)`

Explanation: In this example, we have a dataset with two columns, A and B, containing multiple values. Using the VAR function with the range A1:B3, we calculate the variance of all the numbers in the specified range. The result in cell C1 will be 5.25.

๐ Part 3: Tips and Tricks ๐

1. When using the VAR function, ensure that the dataset you provide contains numerical values. Non-numeric values or empty cells may result in errors or inaccurate variance calculation.
2. If you want to calculate the population variance instead of the sample variance, use the VARP function.
3. You can combine the VAR function with other functions in Excel to perform more complex calculations. For example, you can use IF statements to calculate the variance based on specific criteria conditionally.
5. Remember to appropriately use relative or absolute cell references when applying the VAR function to different datasets or ranges.
6. If you’re working with a large dataset, you can use named ranges to make your formulas more manageable and easier to understand.
More Examples:

๐ Example 3:

A
14
29
36
42
57

๐ Formula: In cell B1, enter `=VAR(A1:A5)`

Explanation: In this example, we have a dataset with five values (4, 9, 6, 2, and 7). We calculate the variance of these numbers by using the VAR function with the range A1:A5. The result in cell B1 will be 6.8.

๐ย Example 4:ย

AB
12.51.2
23.74.9
36.12.3
44.93.8
55.31.9

๐ Formula: In cell C1, enter `=VAR(A1:B5)`

Explanation: In this example, we have a dataset with two columns, A and B, containing decimal values. Using the VAR function with the range A1:B5, we calculate the variance of all the numbers in the specified range. The result in cell C1 will be 3.265.

๐ Example 5:ย

A
13.4
27.8
35.2

๐ Formula: In cell B1, enter `=VAR(A1:A3)`

Explanation: In this example, we have a dataset with three decimal values (3.4, 7.8, and 5.2). We calculate the variance of these numbers by using the VAR function with the range A1:A3. The result in cell B1 will be 4.9867.

๐ย Example 6:ย

ABC
110512
21589
320614

๐ Formula: In cell D1, enter `=VAR(A1:C3)`

Explanation: In this example, we have a dataset with three columns, A, B, and C, containing multiple values. Using the VAR function with the range A1:C3, we calculate the variance of all the numbers in the specified range. The result in cell D1 will be 17.8889.

๐ Example 7:ย

A
1100
2200
3300
4400
5500
6600
7700
8800
9900

๐ Formula: In cell B1, enter `=VAR(A1:A9)`

Explanation: In this example, we have a dataset with nine values ranging from 100 to 900. We calculate the variance of these numbers by using the VAR function with the range A1:A9. The result in cell B1 will be 75000.

๐ Example 8:

A
13,000
23,500
34,200
42,800

๐ Formula: In cell B1, enter `=VAR(A1:A4)`

Explanation: This example has a dataset with four values representing monetary amounts. We calculate the variance of these numbers by using the VAR function with the range A1:A4. The result in cell B1 will be 140,000.

๐ย Example 9:ย

AB
15Cat
23Dog
38Bird

๐ Formula: In cell C1, enter `=VAR(A1:A3)`

Explanation: In this example, we have a dataset with numbers in column A and corresponding animal names in column B. By using the VAR function with the range A1:A3, we calculate the variance of the numerical values. The result in cell C1 will be 6.3333. It’s important to note that non-numeric values, like the animal names in column B, are ignored in the variance calculation.