1. User's Guide
  2. Working With Grids
  3. Working With Rows and Columns in Grids
  4. Defining Formula Rows and Columns

Defining Formula Rows and Columns

A formula consists of a combination of grid references, mathematical functions, and arithmetic operators. A grid reference uses values from the current grid or another grid in a calculation. You can define arithmetic formulas on formula rows, columns, cells, or a range of formula cells in a grid. For example, you can build a formula that adds one or more rows. Or, you can multiply two rows, add a third row, then display the result. For a explanation of the mathematical functions you can use in formulas, see Mathematical Functions.

When defining formula rows or columns, keep in mind the following points:

  • Formula results are calculated based on the underlying data values in one or more specified cells. If the cells used in a formula are formatted to use scaling, the formula results may differ from the displayed values for the cells, since the underlying, unformatted data values are used in the formula.

  • By default, cells that contain missing data (#MISSING) are not treated as zero (0) in formulas, unless specified differently using the "IfNonNumber" property. In a formula that uses division, an error is returned.

  • Use do notation to specify mathematical properties. Dot notation is a syntax that specifies properties for a grid or another property. Specify the property using a period ( . ) followed by the property name.

  • When using the Oracle Hyperion Financial Management database connection, the results vary, based on the settings of the MissingValuesAreZeroInFormulas (formerly MissingValuesAreZeroInFormulasInHFM) option in the JConsole.exe file. For information on JConsole, see the Oracle Hyperion Financial Reporting Administrator's Guide . If the property is set to one (1), the #Missing value is treated as a zero. However, if the property is set to zero (0), the #Missing value is treated as missing which results in #missing or #error values. The default setting is one (1).