Syntax. VARA(value1,value2,...)
Definition. This function estimates the variance based on a sample. Not only numbers but also text and logical values (TRUE and FALSE) are included in the calculation.
Arguments
value1 (required) and value2 (optional). At least one and up to 255 value arguments (30 in Excel 2003 and earlier versions) corresponding to a sample of the population.
Note
The VARA() function assumes that its arguments are a sample of the entire population. If your data represents the entire population, calculate the variance with VARPA().
The difference between VAR.S() and VARA() is that VARA() evaluates arguments with the value TRUE to 1 and arguments with text, or FALSE to 0. If you do not want to include logical values and text in the calculation, use the VAR.S() function.
Background. Because the only difference between VARA() and VAR.S() is that VARA() includes text and logical values, this example focuses on VARA().
VARA() uses the same formula as VAR.S():
The value x is the sample mean AVERAGE(value1,value2,...), and n is the sample data set.
Example. Let’s look again at the evaluation of the website visits. Because in the past 18 months several website problems occurred, the visits could not be counted in all months. The company had the following problems:
In May 2007 and August 2007, a problem with website hosting occurred, and in both months the website was not available. These months are marked with the string “hostingproblems”.
In March 2008, the content pages of the product area were changed to include the entire area. Because external access to the product area wasn’t possible, this month is marked with the logical value FALSE.
As you can see in Figure 12-152, the VARA() function returns a different result than VAR.S(). This happens because VARA() includes text and logical values. Because this example uses text and the logical value FALSE, both values are set to 0.
Figure 12-152. Calculating the variance with the VARA() function, including text and logical values.
If you take a closer look at the DOWNLOAD area, you can make the following statement: The average squared deviation of the values from the arithmetic mean based on text and logical values is 443,737 for the DOWNLOAD area.