Setting Up Report Formulas: Regular report, web application

To determine calculation options for formulas with circular references, reference style, use of tooltips during formula entry, and error indication, use the Formulas group of settings of the Settings panel.

Select the Settings main menu item.

The Formulas tab on the Settings panel can also be opened by selecting the Error Check Parameters item in the error cell's context menu.

When the Iterative Calculations checkbox is selected, it is possible to calculate values of the cells that contain formulas with circular references.

NOTE. This setting is determined for the entire report but circular references are enabled within a single sheet. If cells contain cyclic formulas referring to values other sheets' cells, the Circular References are Found error message appears even if interactive mode is enabled.

By default the checkbox is deselected, and data of the cells that contain circular references, is not calculated. Generally these cells show an error. If the checkbox is selected, formulas are automatically recalculated. When parameters of iterative calculation change, formulas are not recalculated automatically.

If a loop appears on adding a formula to cell, and iterative calculation for circular references is enabled, starting value of this cell is considered to be zero. When the mode is enabled, the loop with defined parameters is iterated each time the report is calculated. The loop ends when the maximum iteration number is reached or when the change of value of each cell becomes less than the relative error.

After selecting the Iterative Calculations checkbox set the following parameters:

Maximum Number of Iterations. Set the number of iterations, after which to stop calculating cells that contain circular references. Default value is 100 iterations.
Relative Error. Set the value to compare with the difference of values of each cell that contains circular references at the current and previous iterations. If the change of value of each cell becomes less than the relative error the calculation of cells containing circular references is stopped at this iteration. Default value of relative error is set to 0.001.

NOTE. Values of the Maximum Number of Iterations and Relative Error parameters must be non-negative numbers.

Two reference styles can be used in regular reports:

A1. Columns are marked with letters, and rows are marked with numbers.
R1C1. Rows (R - row) and columns (C - column) are marked with numbers, and cell references in formulas are set by shifting from the current cell. For example, the cell R[-1]C[2] refers to the cell one row to the top and two columns to the right of the current cell.

By default, the R1C1 Reference Style checkbox is deselected, and the A1 style is used. When the checkbox is selected, the R1C1 style is used to create new links, and headers of sheet columns are indicated with numbers.

To speed up entry and use correct functions in formulas, select the Formula Autocompletion checkbox. The checkbox is selected by default.

If the checkbox is selected, when entering function to a cell or formula bar, the system displays a list of functions, which names contain the entered sequence of characters.

Select the Error Checking checkbox to set up color highlighting in case or errors. In the Error Indicator Color drop-down palette select cell highlighting color.

The setting is available if the Error Checking checkbox is selected.

Click the Reset Errors button to reset indication for the cells that contained errors in formulas but were skipped by the user with the Skip Error command.

To set additional search and error checking rules, determine the following parameters in the Rules of Error Checking group:

Check References to Empty Cells. If the checkbox is selected, references to empty cells are checked.

NOTE. On working with cells take into account features of calculation for certain functions: Average, Max and Min.

Numbers Formatted as a Text. When the checkbox is selected, the corresponding cells contain error indicator and a context button to handle error. Select the checkbox to indicate cells with numbers saved as a text.
If a Formula Refers to an Empty Cell. In the drop-down list select one of the actions to check formulas that refer to empty cells:

Show Error Indicator. Empty cells used in the formula are replaced with zeros. The cell will show formula calculation result, an error indicator and the Actions with Error context button to handle error in the formula when clicking the cell:

Convert into Number. The command converts the value contained in the cell into number. The command is available for cell range that contains such data.

NOTE. This command is available only for the cells with numbers saved in text format.

Ignore Error. The command closes the context menu, removes error indicator from the cell and hides error button.

Edit in Formula Bar. The command turns the cell to formula edit mode. Entry focus goes to the formula bar. If the formula bar is enabled, it will be displayed, and the corresponding checkbox is selected in the View > Show main menu.
Error Check Parameters. The command opens the Parameters panel on the Formulas tab.

NOTE. Only the Skip Error option is available for a cell range.

Do Not Calculate Formula. The cell will show an error message: #REFERENCE! without showing the indicator.
Treat Empty Value as Zero. Empty value will be replaced with zero.