Conditional Inverse Formulas

You can use the INVERSEIF function to define a conditional inverse formula.

This type of function uses the following structure:

<Base function> | INVERSEIF(<Condition>, <Inverse formula>)

The inverse formula is valid when the condition is met; otherwise, it will not be executed. The condition uses the same syntax as the condition in an IF function, and the inverse formula uses the same syntax as an INVERSE function. For more information, see Conditional Formulas Using Dimension Members and Inverse Formulas.

Consider the following example, where a Revenue account uses an IF function to take its value either from the Actuals data or from a Forecast calculation, depending on the date:

IF ([Time].year < 2018, [Revenue_Actuals],[Revenue_Forecast])

You want to enable data entry on this account when it shows forecast data, but not when it shows actuals data. Therefore, you can define the following conditional inverse function:

IF ([Time].year < 2018, [Revenue_Actuals],[Revenue_Forecast]) | INVERSEIF([Time].year >= 2018, [Revenue]:=[Revenue_Forecast])

The account now supports data entry on forecast data from 2018 onwards.

Multiple INVERSEIF functions can be added to a formula, as well as multiple INVERSE functions. These functions are evaluated according to their order in the formula, and the first valid function is applied. Data entry may still be prevented if each inverse function has an unfulfilled condition or invalid target cells.