Variance/Var

Variance is a financial function that evaluates the difference between the specified values based on account type for the current account. For example, for Expense or Liability accounts, a positive result represents a decrease, so the result appears as a negative number. You can use this function with these UDA account types: Asset, Liability, Equity, Revenue, and Expense.

Syntax:

Var (reference1, reference2)

where reference1 and reference2 are references to a row, column, or cell that correspond to members of the same Account dimension whose variance results are to be calculated.

Expected Results

The following table describes the expected results when using the Variance function with Accounts.

Table B-24 Expected Results when Using the Variance Function

Column A	Column B	Var ([A] , [B])>0	Var ([A] , [B])<0
Asset	Asset	Returns a positive value	Returns a negative value
Liability	Liability	Returns a positive value	Returns a negative value
Equity	Equity	Returns a positive value	Returns a negative value
Revenue	Revenue	Returns a positive value	Returns a negative value
Expense	Expense	Returns a negative value	Returns a positive value

Variance Behavior

The Variance function expects comparison of the same account type. When you compare two different account types, like Sales & Expense, the Variance function performs the straight math without applying the logic of the account type. For example:

Table B-25 Results for the Variance Function When Comparing Two Different Account Types

Sales	Expense	Result
-400	100	-500

When the Variance function is applied to a dimension that isn't tagged as an Accounts type, an #ERROR results at runtime.
#MISSING is treated as zero (0), unless specified differently using the ifnonnumber property.

Examples

The Variance function accepts cell, column, or row references only.

Table B-26 Syntax Examples for the Variance Function

Syntax	Example
Sample syntax referencing a column:	`Var ([A], [B])`
Sample syntax referencing a row:	`Var ([3], [4])`
Sample syntax referencing a cell:	`Var (Cell [3,A], [3,B])`

In this example, the variance between column A (Actual) and column B (Budget) is calculated as:

Var([A],[B])

This example produces the following report:

                       		Year   Product Market

                      Actual   Budget Variance

                    ======== ======== ========

Sales(Income)       400,855  373,080    27,775

COGS (Expense)      179,336  158,940   -20,396