VariancePercent/VarPer

VariancePercent is a financial function that evaluates the difference, in percent, between the specified values based on account type for the current account. For example, for an Income, Flow, Asset, or Balance account, a positive result represents an increase, so the result appears as a positive number. For Expense or Liability accounts, a positive result represents a decrease, so the result appears as a negative number.

Syntax:

VarPer (reference1, reference2)

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

Expected Results

The following table describes the expected results when using the VariancePercent function with Accounts tagged with the below UDAs.

Table B-27 Expected Results when Using the VariancePercent Function

Col A	Col B	VarPer ([A] , [B])=0	VarPer ([A] , [B])>0	VaPer ([A] , [B])<0
Asset	Asset	0	Returns a positive value	Returns a negative value
Liability	Liability	0	Returns a negative value	Returns a positive value
Equity	Equity	0	Returns a positive value	Returns a negative value
Revenue	Revenue	0	Returns a positive value	Returns a negative value
Expense	Expense	0	Returns a negative value	Returns a positive value

VariancePercent Behavior

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

  Table B-28 Expected Results of the VariancePercent Function when Two Account Types are Compared

  Sales	Expense	Result
  -400	100	-5.

• When the VariancePercent function is applied to a dimension that isn't of type Accounts, an #ERROR results at runtime.

• #MISSING is treated as zero (0), unless specified differently using ifnonnumber property.

Examples

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

Table B-29 Syntax Examples of the VariancePercent Function

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

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

VarPer([A],[B]) 

Example Result of the VariancePercent Function when Comparing Columns

This example produces the following report: