# Perform calculations on data

- By Curtis Frye
- 4/9/2019

- Name groups of data
- Create formulas to calculate values
- Summarize data that meets specific conditions
- Set iterative calculation options and enable or disable automatic calculation
- Use array formulas
- Find and correct errors in calculations
- Skills review
- Practice tasks

## Set iterative calculation options and enable or disable automatic calculation

Excel formulas use values in other cells to calculate their results. If you create a formula that refers to the cell that contains the formula, the result is a circular reference.

Under most circumstances, Excel treats a circular reference as a mistake for two reasons. First, the vast majority of Excel formulas don’t refer to their own cell, so a circular reference is unusual enough to be identified as an error. The second, more serious consideration is that a formula with a circular reference can slow down your workbook. Because Excel repeats, or iterates, the calculation, you need to set limits on how many times the app repeats the operation.

You can control your workbook’s calculation options by using the controls on the Formulas page of the Excel Options dialog box.

**The Calculation Options section of the Excel Options dialog box has three available settings:**

**Automatic**The default setting; recalculates a worksheet whenever a value affecting a formula changes**Automatic except for data tables**Recalculates a worksheet whenever a value changes, but doesn’t recalculate data tables**Manual**Requires you to press F9 or, on the Formulas tab, in the Calculation group, click the Calculate Now button to recalculate your worksheet

You can also use options in the Calculation Options section to allow or disallow iterative calculations. If you select the Enable Iterative Calculation check box, Excel repeats calculations for cells that contain formulas with circular references. The default Maximum Iterations value of 100 and Maximum Change of 0.001 are appropriate for all but the most unusual circumstances.

**To recalculate a workbook**

Display the workbook you want to recalculate.

Press

**F9**.*Or*On the

**Formulas**tab, in the**Calculation**group, click**Calculate Now**.

**To recalculate a worksheet**

Display the worksheet you want to recalculate.

In the

**Calculation**group, click the**Calculate Sheet**button.

**To set worksheet calculation options**

Display the worksheet whose calculation options you want to set.

In the

**Calculation**group, click the**Calculate Options**button.Click the calculation option you want in the list.

**To set iterative calculation options**

Display the Backstage view, and then click

**Options**.In the

**Excel Options**dialog box, click**Formulas**.In the

**Calculation options**section, select or clear the**Enable iterative calculation**check box.In the

**Maximum Iterations**box, enter the maximum iterations allowed for a calculation.In the

**Maximum Change**box, enter the maximum change allowed for each iteration.Click

**OK**.