Calculations

This chapter explains Objective Grid calculations, including:

How Objective Grid calculates,

How to change calculation options such as automatic recalculation and iteration limit, and

Elements of formulas.

How Objective Grid Calculates

Objective Grid uses natural order recalculation, which is a method of ordering the computations such that each cell's value is computed only after the values for all cells on which it depends have been computed. Natural order recalculation guarantees that cells are always computed correctly, regardless of the order in which the cell formulas were entered. Minimized grid windows are recalculated the same as visible sheets.

Objective Grid supports several recalculation options:

Mode (Manual or Automatic)

Method (Foreground or As Needed)

Iteration Limit

Constraint Checking

Precision

Mode of Recalculation

Objective Grid supports two modes of recalculation: Manual and Automatic. When Manual is set, formulas are only recomputed when an explicit recalculation operation is requested. When Automatic is set, any action which causes the contents of a cell to change automatically triggers recalculation of any formulas depending on the changed cell.

Method of Recalculation

Recalculation is the process whereby Objective Grid re-computes the mathematics of the worksheet after the user makes changes. Objective Grid lets you designate whether recalculation takes place on demand (manual mode) or after any change is made (automatic mode).

Natural recalculation is a method of calculation that follows a natural hierarchy of the importance of cell dependencies.

Automatic recalculation supports two methods of recalculations: Foreground and As Needed.

Foreground

When Foreground is set, Objective Grid enforces minimal recalculation, which means that only the cells in the spreadsheet that are potentially affected by an operation that you perform (such as editing a cell, or moving a range of cell) are recalculated. This feature minimizes the work performed during recalculation and thus speeds up your computations. Minimal recalculation is in effect whenever the recalculation is set to Automatic and the method is set to Foreground.

As Needed

When As Needed is set, Objective Grid enforces frugal recalculation, which further reduces the number of cells that need to be computed. The performance gain in recalculation can be significant, depending on the size and complexity of the sheet. Frugal recalculation is in effect whenever the recalculation mode in the Recalc Options dialog is set to Automatic and method is set to As Needed.

NOTE >> As Needed does not support Constraint Checking and Iterative Recalculation options. If all of this seems a bit confusing, refer to Figure 126 for clarification.

Iterative Calculations

Normally, a formula in a given cell should not depend on that cell itself, either directly or indirectly. A circular reference is when two or more cells are defined by each other. Such a condition is called a cyclic dependency. When cyclic dependencies exist, the rule for natural order recalculation as described above does not make sense. When you enter a formula that creates a cyclic dependency, the message "Cycle!" is displayed in the cell.

In some cases cyclic dependencies are useful in that they can represent iterative calculations, which Objective Grid supports. Iterative calculation is a method of calculation whereby Objective Grid passes through the worksheet more than once, addressing circular references or performing goal-seeking. Iterative calculation is useful when two or more cells mutually depend on each other such that each time they are recalculated, their values become closer and closer to the desired answer.

When the Iteration Limit is set to a non-zero value and Method is set to Foreground, iterative calculation is enabled. In this mode, Objective Grid will make multiple recalculation passes, still preserving the natural order and minimal recalculation rules described above, until either the iteration limit has been reached or, if constraint checking is enabled, until all constraints are satisfied. The @INIT function helps perform iterative calculations.

NOTE >> A forward reference is a cell whose value depends on values calculated later in a spreadsheet.

Constraint Checking

Constraint checking is a process whereby Objective Grid checks constraint expressions while recalculating the worksheet. If constraint-checking is disabled in the Default Sheet Characteristics dialog box, Objective Grid ignores constraint expressions.

A constraint expression is an expression appended to a formula that establishes conditions under which the formula operates or boundaries for valid results of the formula.

Objective Grid formulas may contain conditional expressions which establish conditions under which a formula operates or boundaries for valid results of the formula. When calculating the spreadsheet, Objective Grid ignores constraint expressions unless constraint checking is enabled and the recalculation method is set to Foreground for the spreadsheet.

Precision

Objective Grid performs all calculations in double-precision. Calculations with logical operators - ! (logical NOT), && (logical AND), || (logical OR), and ?: (conditional) - consider a non-zero value to be True and a zero value to be False. Integer operators - ~ (complement), & (bitwise AND), | (bitwise OR), ^ (bitwise EXCLUSIVE-OR), and % (modulus) convert their operands to 32-bit integers before performing the operation.

Figure 126 – Recalculation Modes and Methods