The Excel **VAR **function is categorized as a Statistical function of Microsoft Excel. It determines the risk of any investment and, hence, is important for financial organizations. In this article, we will discuss the use of the Excel **VAR** function with examples and proper illustrations.

From the above image, you will get a brief idea of getting the variance of a data set.

**Table of Contents**hide

## VAR Function in Excel: Syntax

**Function Objective:**

The **VAR **function returns the variance of a sample taken from population data.

**Syntax:**

**VAR(number1,[number2],â€¦)**

**Argument:**

ARGUMENT |
REQUIRED/OPTIONAL |
EXPLANATION |
---|---|---|

number1 |
REQUIRED |
The first number argument is related to a sample population. |

number2,.. |
OPTIONAL |
Additional sample population. We can add up to 255 numbers. |

**Return Parameter:**

It returns a numeric value.

**Available in:**

Excel for Microsoft 365, Microsoft 365 for Mac, Excel for the web, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2019 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel for Mac 2011, Excel Starter 2010

**Note:**

This function has been replaced by one or more new Excel functions. Those functions may provide better accuracy. Still, this function is available for the older versionâ€™s compatibility. But we should practice using the new functions as well because this function may not be available in future versions, according to support.microsoft.com.

## Different VAR Functions in Excel

We have multiple **VAR **functions according to their usage in Microsoft Excel. They are as follows:

Name |
Data Set Type |
Text and logical |
---|---|---|

VAR |
Sample |
Ignored |

VARP |
Population |
Ignored |

VAR.S |
Sample |
Ignored |

VAR.P |
Population |
Ignored |

VARA |
Sample |
Evaluated |

VARPA |
Population |
Evaluated |

**Note:**

The **VAR **function is now modified to **VAR.S** and used in the latest versions of Microsoft Excel. We can use **VAR.S** instead of **VAR** and get the same result.

## What is Variance?

Variance is the amount of variability of the data set, which indicates how different is spread among values. Mathematically, it is the average of the square values of the difference. The basic formula for calculating variance is as follows:

Here,

**x** = Sample values, **A**= Mean or the average of the sample values, **n** = Number of sample values.

## How to Use the VAR Function in Excel

Now, we will show how to use this Excel **VAR **function. We have taken data from some students with their height and weight. Now, we will calculate the variance of the height of the students here.

**Step 1:**

- Add a box for the Variance.

**Step 2:**

- Now, type the
**VARÂ**function. - The formula will be:

`=VAR(C5:C11)`

**Step 3:**

- Now, press
**Enter**.

We get the variance here.

**Calculating the Variance in Excel (Manual Method)**

We can crosscheck and show the calculation manually.

**Step 1:**

- First, find the mean of the range using
**the AVERAGEÂ function**.

`=AVERAGE(C5:C11)`

**Step 2:**

- Now, subtract the mean value from each of the sample values. Apply the following formula:

`=C5-$C$13`

- Put the formula for the next cells in the same way.

**Step 3:**

- Now, calculate the square of each difference using the formula below.

`=D5^2`

**Step 4:**

- Now, we will find out the variance by applying the following formula:

`=SUM(E5:E11)/(COUNTA(B5:B11)-1)`

**Step 5:**

- Then press
**Enter**.

The final output is the same as weâ€™ve got using the **VAR** function first.

We can use the **VAR **function in different ways. We will show the use of this function with the following examples. Letâ€™s see one by one.

## 1. Using Excel VAR Function to Calculate Variance of Individual Numbers

In this example, we will insert individual numbers in the formula. We will consider the data below for this example.

Letâ€™s see the steps below.

**Step 1:**

- Go to cell
**C13**. - Write the
**VAR**function and input the data individually. - So, the function will become-

`=VAR(53,56,50,60,65,62,61)`

**Step 2:**

- Then press
**Enter**.

This is the required variance.

## 2. Getting Variance of a Single Range with VAR Function

Here, we will get the variance of a single range by applying the **VAR** Function. We have only a single range in our data set.

Letâ€™s go through the following steps.

**Step 1:**

- We will insert a range in the
**VARÂ**function. - So, the formula will be:

`=VAR(C5:C11)`

**Step 2:**

- Then press
**Enter**.

Here, we get the variance of a single range.

## 3. Applying Excel VAR Function with Multiple Ranges

This example shows the use of the VAR function with multiple ranges of data. If we need to find variance from different columns, then we will apply this way.

We have taken the below data for this example.

Now, see the following steps.

**Step 1:**

- Now, write the
**VARÂ**function. - Input both ranges using a comma between them.
- So, the formula is:

`=VAR(C5:C11,E5:E11)`

**Step 2:**

- Then, press
**Enter**.

Here, we show when we have multiple ranges of data and how we will apply the **VAR **function.

## 4. Applying Number Arrays with VAR Function in Excel

We can also use an array of numbers with the **VAR **function. In this example, we insert an array together with a range. See how we do this in the following manner.

**Step 1:**

- Type the formula in cell
**C13**:

`=VAR(C5:C11,{50,52})`

**Step 2:**

- Now, press
**Enter**.

We get the variance using the array of numbers.

## Use of Other VAR Functions to Calculate Variance

### 1. VAR.S Function as a Counterpart of VAR Function

The** VAR.S** Function does the same operation as the **VAR **function. The **VAR.S** function is available in Excel 2010 and later versions.

The arguments of **VAR.S** are also the same as the **VAR** function.

We can show that the return of **VAR **and **VAR.S** functions are the same from the below image.

We have just typed the following formulas.

Type the following formula in cell** C13-**

`=VAR(C5:C11)`

Enter the formula in cell** C14-**

`=VAR.S(C5:C11)`

### 2. VARA Function That Considers Boolean Logics and Text Values

The** VARA **function is a bit different from the **VAR **and **VAR.S** functions. The **VAR **and **VAR.S** functions ignore the Boolean and Text values. But the **VARA **function evaluates the text as **0**, **FALSE** as **0**, and **TRUE** as **1**. So, it is important to carefully choose the suitable Excel function when we calculate the variance.

The **VARA **function is like this:

For this example, weâ€™ve taken the following dataset where text value exists.

Now, weâ€™ll show the difference between the **VARA** function with the **VAR **and **VAR.S** function.

Apply **VAR**, **VAR.S**, and **VARA** functions in Cell** C13**, **C14**, and **C15**.

Enter the following formula in cell** C13**.

`=VAR(C5:C11)`

The formula in cell** C14** will be-

`=VAR.S(C5:C11)`

The formula in cell** C15** is-

`=VARA(C5:C11)`

Here, we can see that from **VAR **and **VAR.S,** we get the same result. But the **VARA **returns a different result from them. Because the **VARA **function counts the text values as **0**.

### 3. Use of VARP and VAR.P to Calculate Population Variance

**VARP **and **VAR.P** are the same functions. The **VARP **is applicable for Excel 2000 to 2019, and **VAR.P** is applicable for Excel 2010 to later versions. Both functions are used to get population variance. It means how data in the entire population is spread out. The mathematical formula for population variance is:

Here,

**x** = Sample values, **A**= Mean or the average of the sample values, **n** = Number of sample values.

Now, we will find out the population variance from the below data.

**Step 1:**

- Write the
**VARP**function in cell**C13**. - Now, the complete formula will be-

`=VARP(C5:C11)`

**Step 2:**

- Now, press
**Enter**.

**Step 3:**

- Now, we will apply the
**VAR.P**function on cell**C14**. - The complete formula is:

`=VAR.P(C5:C11)`

**Step 4:**

- Again, press
**Enter**.

Here, we showed the use and result of the **VARP** and **VAR.P** function.

### 4. VARPA Function That Considers Boolean Logics and Text Values

The **VARPA **and **VARP **and **VAR.P** do the same operation. But **VARP **and **VAR.P** ignore the texts and boolean logic. On the other hand, the **VARPA **evaluates the text as **0**, **TRUE** as **1**, and **FALSE** as **0**, and hence gives a different result.

Assume that some studentsâ€™ weight is not available. So, the data set will look like this:

**Step 1:**

- Now, get the
**VARP**and**VAR.****P**by applying the following formulas in cell**C13**and cell**C14.** - Formula in cell
**C13**is:

`=VARP(C5:C11) `

- The formula in cell
**C14**is:

`=VAR.P(C5:C11)`

**Step 2:**

- Now, we will get the
**VARPA**in the same way in cell**C15**.

`=VARPA(C5:C11)`

Here, we get population variance for different population functions that exist in Excel.

## Things to Remember

- When the function contains less than 2 numeric values, an error will occur; that error is
**#DIV/0!**. - If any text value is supplied to the function, it will show an error.
- In case we use the entire population, then we must apply
**VARP**to compute the variance. - If we include logical values and texts in the function argument, then we must use the
**VARA**function to calculate variance.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

In this article, we showed the use of the **VAR **function in Excel with suitable examples. I hope this article will help you.