Calculation Options

The settings to control formula calculation can be found in two places.


Formulas Tab

These options can be displaying by selecting File > Options - Formulas Tab.

alt text

All these settings are workbook specific but are applied at an application/session level.
The calculation settings are taken from the first workbook you open (in that session) and then ignored in subsequent workbooks.
Automatic - (Application Setting) Calculates all dependent formulas every time you make a change to a value, formula or name. Any workbook that contains formulas will be automatically calculated when it is opened. This is the default calculation setting.
Automatic except tables - Calculates all dependent formulas except those in data tables. You can calculate the formulas in data tables by pressing the "Calc Now" button,
Manual - Calculates only when requested by the user. "Calculate" will appear in the status bar when you have uncalculated formulas.
Recalculate before save - Calculates all dependent formulas in the worksheet before it is saved.
Enable iteration calculation - This can be used to restrict the iteration when using circular references. The Maximum iterations and Maximum changes options should be disabled when this checkbox is not ticked. There is no iteration (circular references cannot be calculated) when this checkbox is cleared. The default is not ticked.
Maximum iterations - This is the maximum number of iterations used before iteration will stop. The default is 100.
Maximum change - The largest number that the change in value, must be less than, before iteration will stop. The smaller the number the more accurate the result. The default is 0.001.


Advanced Tab, Formulas

These options can be displaying by selecting File > Options - Advanced Tab and scrolling down to the Formulas group.

alt text

Enable multi-threaded calculation - (Added in 2007). Enables fast calculation by using all of the processors on your computer, or by using the number of processors that you type manually. Excel only supports using up to 64 processor cores. The default is ticked.
Number of calculation threads - (Added in 2007). Lets you specify the number of processors that are used for calculation.
Use all processors on this computer - (Added in 2007). Uses all of the processors that are available on your computer. The default is ticked.
Manual - (Added in 2007). Lets you specify the number of processors that you want to use. In the Manual box, enter a positive number between 1 and 1024. This number can be more than the number of processors on the computer.
Allow user-defined XLL functions to run on a compute cluster - (Added in 2013). This option is only enabled if you have a High Performance Computing (HPC) Cluster Connector. A Cluster Connection enables you to run cluster-safe XLL functions remotely on a HPC Cluster for increased performance.
Cluster type - (Added in 2013). This option is only enabled if you have a HPC Cluster Connector.
Options - (Added in 2013). This option is only enabled if you have a HPC Cluster Connector.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext