Enrollment for the FMVA™ Certification Program is now OPEN!

VARA Function

Calculates the sample variance of a given set of values

What is the VARA Function?

The VARA Function is categorized under Statistical functions. VARA is very similar to the VAR function. It will calculate the sample variance of a given set of values.

Variance is a statistical measure used across a set of values to identify the amount that the values vary from the average value.

As a financial analyst, the VARA Function can be useful in calculating variance in revenue. Portfolio managers often use this function to measure and keep a track of their portfolio risks.

 

Formula

=VARA(number1,[number2],…)

 

The VARA function uses the following arguments:

  1. Number1 (required argument) – It is the first argument corresponding to a population.
  2. Number 2,.. (optional argument) – Here, the number arguments can be up to 254 values or arrays of values that provide at least two values to the function. However, we can only enter up to 30 number arguments in Excel 2003 and earlier versions.

 

When providing the arguments, we need to remember that:

  1. We can enter up to 254 number arguments to the VARA function in current versions of Excel (2007 and later). However, we can only enter up to 30 number arguments in Excel 2003.
  2. VARA is used when we are calculating the variance for a sample of a population. If we wish to calculate the variance for an entire population, we should use the Excel VARP function.

 

VAR vs. VARA function

Both the functions are similar but differ in the following ways:

  1. When an array of values containing logical values is provided, VAR ignores logical values whereas VARA will assign the value 0 for the FALSE logical value and 1 for the TRUE logical value.
  2. Text – including empty text “”, text representations of numbers, or other text – within arrays or reference arguments are ignored by VAR but counted as zero by VARA.

 

How to use VARA to calculate variance in Excel?

As a worksheet function, VARA can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:

 

Example

Suppose we are given data about the height of children from three cities, as shown below:

 

VARA Function

 

For variance, the formula used is:

 

VARA Function - Example 1

 

We get the results below:

 

VARA Function - Example 1a

 

In the example above, we took the arguments for the VARA function as input in the form of three cell ranges. However, we can also enter the figures directly, either as individual numbers or number arrays.

 

Few things to remember about the VARA Function

  1. #DIV/0 error – Occurs if we did not provide any numeric values as arguments.
  2. #VALUE! error – Occurs when any of the values provided directly to this function are text values that cannot be interpreted as numbers.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!