To determine calculation options for formulas with circular references and determine a reference style, go to the Formulas tab of the Parameters panel.
Select the Tools > Parameters main menu item.
The Formulas tab on the Parameters panel can also be opened by selecting the Error Check Parameters item in the error cell's context menu.
Determine the following parameters on the tab:
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.
If the Error Checking checkbox is selected, cells containing errors can be indicated.
After the checkbox is selected, determine settings of the action to execute and highlighting to apply if some errors occur:
Error Indicator Color. Select a color for cells highlighting in the drop-down palette.
Reset Ignored Errors. Click the button to reset indication for the cells that contained errors, 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.
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. Default value. The cell will show an error indicator and the Actions with Error context button to handle error in the formula when the cell gets focused.
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.
To indicate cells with numbers saved in text format, select the Numbers Formatted as a Text checkbox. When the checkbox is selected, the corresponding cells contain error indicator and a context button to handle error.
See also: