How to Define a Formula?

1. In SpotlightXL, navigate to the Model > Formula.

2. Select the model for which you want to define the formula from the Model list box.

3. Select the Dimension, which contains the data you want to perform the formula and calculation on.
   

   

4. Enter a name to identify the formula when adding a new formula.

5. Identify the dimensions for which the formula will be applied.

6. Select filter properties to apply to the formula. The following options are available:  

   1. AllMembers – The formula will apply to all members that are part of the dimension.

   2. LevelandAbove – The formula will apply to the level and above the filtered value.

   3. MemberandBelow – The formula will apply to all data for the selected value and below (such as leaf level).

   4. FixedMember – The formula will apply to a specific member of the dimension.
      

      

7. Based on Filter selected, enter a value in the Filter Value field. For example, if FixedMember is selected as the Filter for Time and you want the formula applied to the 2019 scenario, you would enter 2019 in the Filter Value field.

8. In the Formula Design cells, enter formulas for calculated dimensions.
   

   

9. Click Save.

10. Run the calculation on the Calculation page when new formulas are included. First, add the formula to the calculation and then run the calculation.
    

    

Note:

The formula filter is case-sensitive.

Create formulas using Microsoft Excel syntax. The following functions are supported in SpotlightXL formulas:

• Arithmetic Operators: + - / *

• Logical Operators used with IF function:= <> < <= > <=

• Functions: IF/THEN, AND, OR, ROUND, FLOOR, CEILING, LN, ISBLANK, ABS, SQRT, POWER, MOD

Tip:

All function and operator syntax is similar to Excel syntax.