Setting Up Report Formulas

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. On changing parameters of iterative calculation, 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 this 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 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.

Features of Iterative Calculation Setup

Using the Fore language, the user can define different settings of iterative calculation for each sheet, that is why the following features should be considered.

On opening the dialog box:

• If iterative calculations are enabled for at least one sheet, the Iterative Calculations checkbox is selected.

• When all sheets have the same parameters of iterative calculations (iterations, error), these settings are displayed on the Formulas tab in the Parameters dialog box.

• If parameters of at least one sheet differ from the others, default parameters are displayed in the dialog box (100 iterations, error 0.001).

On adding a new sheet:

• When iterative calculations are enabled at least for one sheet, they are enabled for the new sheet.

• When all sheets have the same parameters of iterative calculations (iterations, error), a new sheet is created with the same settings.

• If at least one sheet has different parameters, a new sheet is created with default parameters (100 iterations, error value is 0.001).

Two reference styles can be used in regular reports:

• A1. When A1 style is used, columns are marked with letters and rows are marked with numbers.

• R1C1. On using the R1C1 style rows and columns are marked with numbers, and references for cells 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. The cell will show an error indicator and a context button to manage 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 disabled, it appears, and the corresponding checkbox is selected on the View ribbon tab.

• Error Checking Options. The command opens the Report Parameters dialog box 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 is replaced with zero. Default.

To indicate cells with numbers saved in text format, select the Numbers Formatted as a Text checkbox. If the checkbox is selected, the corresponding cells contain error indicator and a context button to manage error.