Part II: Working with Formulas and Functions

This formula will always return the sum of all the data (excluding calculated Total Row values, if any), even if rows or columns are added or deleted. And if you change the name of Table1, Excel will adjust formulas that refer to that table automatically. For example, if you renamed Table1 to AnnualData (by using the Name Manager, or by using Table Tools Design Properties Table Name), the preceding formula would change to

=SUM(AnnualData)

Most of the time, you want to refer to a specific column in the table. The following formula returns the sum of the data in the Actual column:

=SUM(Table1[Actual])

Notice that the column name is enclosed in square brackets. Again, the formula adjusts automatically if you change the text in the column heading.

Even better, Excel provides some helpful assistance when you create a formula that refers to data within a table. Figure 10.13 shows the formula Autocomplete helping to create a formula by showing a list of the elements in the table. Notice that, in addition to the column headers in the table, Excel lists other table elements that you can reference: #All, #Data, #Headers, #Totals, and @ - This Row.

FIGURE 10.13

The formula Autocomplete feature is useful when creating a formula that refers to data in a table.

Correcting Common Formula Errors

Sometimes, when you enter a formula, Excel displays a value that begins with a hash mark (#). This is a signal that the formula is returning an error value. You have to correct the formula (or correct a cell that the formula references) to get rid of the error display.

Tip

If the entire cell is filled with hash-mark characters, the column isn’t wide enough to display the value. You can either widen the column or change the number format of the cell. n

218

Chapter 10: Introducing Formulas and Functions

In some cases, Excel won’t even let you enter an erroneous formula. For example, the following formula is missing the closing parenthesis:

=A1*(B1+C2

If you attempt to enter this formula, Excel informs you that you have unmatched parentheses, and it proposes a correction. Often, the proposed correction is accurate, but you can’t count on it.

Table 10.3 lists the types of error values that may appear in a cell that has a formula. Formulas may return an error value if a cell to which they refer has an error value. This is known as the ripple effect — a single error value can make its way into lots of other cells that contain formulas that depend on that one cell.

TABLE 10.3

Handling circular references

When you’re entering formulas, you may occasionally see a Circular Reference Warning message, shown in Figure 10.14, indicating that the formula you just entered will result in a circular reference. A circular reference occurs when a formula refers to its own value — either directly or indirectly. For example, you create a circular reference if you enter =A1+A2+A3 into cell A3 because the formula in cell A3 refers to cell A3. Every time the formula in A3 is calculated, it must be calculated again because A3 has changed. The calculation could go on forever.

219

Part II: Working with Formulas and Functions

FIGURE 10.14

If you see this warning, you know that the formula you entered will result in a circular reference.

When you get the circular reference message after entering a formula, Excel gives you two options:

•Click OK, and Excel displays a Help screen that tells you more about circular references.

•Click Cancel to enter the formula as is.

Regardless of which option you choose, Excel displays a message in the left side of the status bar to remind you that a circular reference exists.

Warning

Excel won’t tell you about a circular reference if the Enable Iterative Calculation setting is in effect. You can check this setting in the Formulas section of the Excel Options dialog box. If Enable Iterative Calculation is turned on, Excel performs the circular calculation exactly the number of times specified in the Maximum Iterations field (or until the value changes by less than 0.001 or whatever value is in the Maximum Change field). In a few situations, you may use a circular reference intentionally. In these cases, the Enable Iterative Calculation setting must be on. However, it’s best to keep this setting turned off so that you’re warned of circular references. Usually a circular reference indicates an error that you must correct. n

Usually, a circular reference is quite obvious and easy to identify and correct. But when a circular reference is indirect (as when a formula refers to another formula that refers to yet another formula that refers back to the original formula), it may require a bit of detective work to get to the problem.

On the CD

The companion CD-ROM contains a workbook that demonstrates an intentional circular reference. This file is named circular reference.xlsx.

Specifying when formulas are calculated

You’ve probably noticed that Excel calculates the formulas in your worksheet immediately. If you change any cells that the formula uses, Excel displays the formula’s new result with no effort on your part. All this happens when Excel’s Calculation mode is set to Automatic. In Automatic Calculation mode (which is the default mode), Excel follows these rules when it calculates your worksheet:

•When you make a change — enter or edit data or formulas, for example — Excel calculates immediately those formulas that depend on new or edited data.

220

Chapter 10: Introducing Formulas and Functions

•If Excel is in the middle of a lengthy calculation, it temporarily suspends the calculation when you need to perform other worksheet tasks; it resumes calculating when you’re finished with your other worksheet tasks.

•Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends on the result of a formula in cell D11, Excel calculates cell D11 before calculating D12.

Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you’ll find that processing can slow to a snail’s pace while Excel does its thing. In such a case, set Excel’s calculation mode to Manual — which you can do by choosing Formulas Calculation Calculation Options Manual (see Figure 10.15).

FIGURE 10.15

You can control when Excel calculates formulas.

Intentional Circular References

You can sometimes use a circular reference to your advantage. For example, suppose your company has a policy of contributing 5 percent of its net profit to charity. The contribution itself, however, is considered an expense — and is therefore subtracted from the net profit figure. This produces a circular reference (see the accompanying figure).

The Contributions cell contains the following formula:

=5%*Net_Profit

The Net Profit cell contains the following formula:

=Gross_Income-Expenses-Contributions

These formulas produce a resolvable circular reference. If the Enable Iterative Calculation setting is on, Excel keeps calculating until the Contributions value is, indeed, 5 percent of Net Profit. In other words, the result becomes increasingly accurate until it converges on the final solution.

221