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 |
|
Sample syntax referencing a row |
|
Sample syntax referencing a cell |
|
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: