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

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

## Download Practice Workbook

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

## Introduction to VAR Function

**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 usages in Microsoft Excel. They are as below:

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 of 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.

**Crosscheck the Result from VAR Function with a Manual Procedure: **

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 formula for the next cells in the same way.

**Step 3:**

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

`=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 we’ve got using the **VAR** function first.

**Read More:** **How to Use COUNTA Function in Excel (3 Suitable Examples)**

## 4 Examples of Using VAR Function in Excel

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. Find Out Variance of Individual Numbers Using VAR Function

In this example, we will insert individual numbers in the formula. We will consider the below data 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. Get Variance of a Single Range Using VAR Function

Here, we will get the variance of a single range 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. Apply 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 then how we will apply the **VAR **function.

### 4. Number Arrays with VAR Function

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.

**Similar Readings**

**How to Use RANK Function in Excel (With 5 Examples)****Calculate Average, Median, & Mode in Excel****How to Use MODE Function in Excel (4 Examples)****Use PROB Function in Excel (3 Examples)****How to Use Excel STDEV Function (3 Easy Examples)**

## 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**.

**Read More:** **How to Use COUNT Function in Excel (With 5 Examples)**

### 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 of 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.

**Read More:** **The Different Ways of Counting in Excel**

## Things to Remember

- When the function contains less than 2 numeric values 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.

## Conclusion

In this article, we showed the use of the **VAR **function in Excel with suitable examples. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.