Calculating Worksheets

Formula One for Java calculates cells in natural order, which means that all dependencies are calculated before their dependents. This ensures that the formulas return correct results.

When a worksheet is edited, Formula One for Java adjusts formula references so they point to the correct cells, then determines the natural order of the formulas.

When data or a formula in a cell changes or when cells are added or deleted, all the formulas on all the worksheets in the workbook are recalculated in order to ensure that data is always valid.

Setting Automatic Recalculation

By default, automatic recalculation is enabled. With this setting, Formula One for Java recalculates the worksheet each time a cell is changed and system processing is idle. For moderately-sized worksheets, recalculation operations happen in a fraction of a second. But for large worksheets or situations where many cells are changed by code, this reorganization and recalculation process can slow system processing.

In these situations, it is sometimes desirable to disable automatic recalculation while your code operates on the worksheet. When the operation is finished, automatic recalculation can be enabled and the worksheet can be updated.

To change automatic recalculation:

  1. Select Tools > Options.
  2. Select the Calculation tab, shown below.

  3. Click OK.

Solving Circular References

Sometimes a formula refers to its own cell, either directly or indirectly. This is called a circular reference. To solve a formula that contains a circular reference, iteration must be used. Iteration is the process of repeatedly calculating a worksheet until a specific condition is met. Formula One for Java supports iteration through settings specified in the Calculation tab of the Options dialog box. The following example includes a circular reference:

Suppose your small business has 10,000 shares of stock owned by four shareholders. You decide to let a fifth shareholder enter your partnership. In return for his investment, you give him 10 percent of the company. How many more shares will the company have to issue to give the new investor 10% of the company?

The following illustration shows the results of this example as it appears in a worksheet.

To control the number of times a circular reference is calculated:

  1. Select Tools > Options and click the Calculation tab.
  2. Click the Iteration check box to limit iteration for calculating circular references.
  3. In the Maximum Iterations text box, type the maximum number of iterations you want Formula One for Java to execute.
  4. In the Maximum Change text box, type the maximum change between iterations. The smaller the number, the more accurate your answer is.
  5. Click OK.

Setting Calculation Precision

Formula One for Java's calculation engine calculates formulas and functions to a high degree of precision. Some of the numbers on your worksheets may be calculated out to many more decimal places than you want. You can specify the number of decimal places to round calculated and constant values so that Formula One for Java doesn't store data you don't need.

You set precision by applying number formats to cells, then telling Formula One for Java to truncate values in the formatted cells to the precision specified by the format. For information on number formats, see About Number Formats.

When you set precision, you set it for all cells that have number formats applied to them in all worksheets in the workbook. You can't set precision for some worksheets in a workbook and not others.

To set calculation precision:

  1. Apply number formats containing the desired number of decimal places to the cells whose precision you want to specify.
  2. Select Tools > Options and click the Calculation tab.
  3. Check the Precision as Displayed checkbox.
  4. The system will display the message "Precision as Displayed will permanently change data accuracy. Continue?"
  5. Click OK to accept or Cancel to dismiss the dialog without setting precision.

Important If you set Precision as Displayed on a workbook that already has values and formulas entered in it, Formula One for Java will round off the constant and calculated values to the specified precision. Once this rounding has taken place, it can't be undone -- the data is lost.