@VARIANCE

The calculation is based upon a sample of a population. Variance is a measure of the dispersion of a set of data points around their mean (average) value.

Syntax

@VARIANCE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, XrangeList)

Parameters

SKIPNONE

Includes all cells specified in the data set, regardless of their content, during calculation of the variance.

SKIPMISSING

Excludes all #MISSING values from the data set during calculation of the variance.

SKIPZERO

Excludes all zero (0) values from the data set during calculation of the variance.

SKIPBOTH

Excludes all zero (0) values and #MISSING values from the data set during calculation of the variance.

XrangeList

A list of numeric values across which the variance is calculated. Referred to generically throughout this topic as "the data set."

Can be a valid member name, a comma-delimited list of member names, cross dimensional members, or a return value from a member set function or range function (including @XRANGE).

For more information about XrangeList, see Range List Parameters.

Notes

@VARIANCE is different from @VAR, which calculates the variance (difference) between two members.
@VARIANCE assumes that the data set (XrangeList) represents a sample of the population. If you want the data set to represent the entire population, use @VARIANCEP.
@VARIANCE is calculated with the "unbiased" or "n-1" method.
@VARIANCE uses the following formula:

${n \sum x^2 - (\sum x)^2}\over n(n-1)$

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Var. This example uses the @RANGE function to generate the data set, and calculates the variance of the sales values for a product family.

FIX (Product)
"Sales Var" = @VARIANCE(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product)));
ENDFIX

This example produces the following report:

                              Jan      New York                                                   
                            Actual        Budget
                            ======        ======                                                
Sales       Colas            678           640
            Root Beer        551           530
            Cream Soda       663           510
            Fruit Soda       587           620
            Diet Drinks      #MI           #MI
             Product        2479          2300

Sales Var   Product         3687.58       4166.67

The following example assumes a Year dimension is added to Sample Basic. It calculates variance using cross-dimensional members in the data set.

FIX(Product)
"Sales Var" = @VARIANCE(SKIPBOTH,@XRANGE("2011"->"Sep", "2012"->"Mar"));
ENDFIX

The above calculation is performed across the following multidimensional range specified by XrangeList:


2011->Sep
2011->Oct
2011->Nov
2011->Dec
2012->Jan
2012->Feb
2012->Mar