Microsoft Office Tutorials and References

In Depth Information

**REVIEW OF EXCEL BASICS**

Understanding Circular Reference Errors

When entering formulas, you might make the mistake of referring to the cell in which you are entering the

formula as part of the formula, even though it should only display the output of that formula. Referring a cell

back to itself in a formula is called a circular reference. For example, say that in cell B2 of a worksheet, you

enter =B2-B1. Excel cannot calculate the answer to this formula because it cannot determine what value to

enter in the formula for B2. A terrible but apt analogy for a circular reference is a cannibal trying to eat himself!

Fortunately, Excel informs you when you try to enter a circular reference into a formula (see Figure C-43).

Excel also warns you if you try to open an existing spreadsheet that has one or more circular references. Before

you can use the spreadsheet, you must fix the formulas that contain circular references.

FIGURE C-43

Excel circular reference warning

Using AND and OR Functions in IF Statements

Recall that the IF function has the following syntax:

IF(test condition, result if test is True, result if test is False)

The test conditions in the previous example IF statements tested only one cell

’

s value, but a test

condition can test more than one value of a cell.

For example, look at the thrift shop tutorial again. The Total Sales Dollars for 2012 depended on the

economic outlook (recession or boom). The original IF statement was =IF($C$8=

,B18*1.3,B18*1.15), as

shown in Figure C-44. This function increased the 2011 Total Sales Dollars by 30% if there was continued

recession, but only increased the total by 15% if there was a boom.

“

R

”

FIGURE C-44

The original IF statement used to calculate the Total Sales Dollars for 2012