Variance Formula

Owned by Migration
Last updated: Oct 10, 2017 by Alexandra Verlaan (Deactivated)

Introduction

The Variance formula allows you to Calculate the variance of two values. The Variance can be expressed as an absolute number or as a relative value.

The syntax of this formula is: (Parameter0 - Parameter1) or in the case of a relative variance (Parameter0 - Parameter1) / ABS(Parameter1)

CONTENT

From the Display Value of drop down list, the user has option to choose between the following:

  • Variance (Default): Variance value will be displayed as (Parameter0 - Parameter 1) or (Parameter0 - Parameter 1) / ABS(Parameter 1) (in case of relative variance)
  • Base: Variance value will be displayed as Parameter 0
  • Reference: Variance value will be displayed as Parameter 1
  • Better/Worse variance representation can be used to display positive variance with positive sign and negative variance with minus sign.


Example 

Variance Settings

In case a report uses a percent calculation in a row and a percent variance in the column, the percent variance will be calculated incorrectly.

As displayed below:

Gross Margin % row 1: is showing a VAR% ( Gross Margin % VAR / Gross Margin parameter0)

Gross Margin % row 2 (with percent point activated):is showing a VAR% ( Gross Margin% parameter0 - Gross Margin% parameter1)

Percent points can be activated on the specific row or column list.

In this example the row list is used to activate the percents points by setting the following settings in the variance settings.


 See also

How_can_I_apply_conditional_formatting?