The VARA function is a statistical function that can calculate and return the variance of a sample by evaluating text and logical values. Variance provides a general idea of how much data points are spread in a data set, and can be used to calculate the standard deviation or determine the risk of an investment. In this guide, we’re going to show you how to use the VARA function and also go over some tips and error handling methods.

Supported versions

  • Excel 2003 and newer versions

VARA Function Syntax

VARA(number1,[number2],…)

Arguments

number1 The first number argument corresponding to a sample of a population.
[number2] The earliest date to retrieve the data. If [interval] is not 0 (daily), the first date can be earlier than the specified date.

VARA Function Example

The VARA function does not accept numeric, text, or logical values as its arguments. While text values and FALSE logical value is evaluated as 0, TRUE becomes 1. You can use range references or static values like in any other formula.

The function calculates the variance using the following function:

  • x: sample mean (average)
  • x ̅: sample element
  • n: sample size

Here is the comparison of the two approaches:

=VARA(sample)

=LET(evaluated,IF(ISNUMBER(–sample),–sample,0),

SUMPRODUCT(POWER(AVERAGE(evaluated)-evaluated,2))/

(COUNT(evaluated)-1))

The example below uses a formula with the named range sample (B5:B10).

Excel VARA Function

We have got help from the LET function to simplify mathematical approach. Check out Function: LET article to learn more.

Download Workbook

Tips

  • The VARA function assumes that its arguments are a sample of the population. If your data represents the entire population, use VARPA instead.
  • The VARA function evaluates text and logical values like TRUE and FALSE. If you want to ignore non-numeric values, check out the VAR.S function.
  • Empty cells are ignored.

Issues

  • Any error in the arguments will cause the function to return an error.