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.
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=
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.
The original IF statement used to calculate the Total Sales Dollars for 2012