Microsoft Office Tutorials and References
In Depth Information
Understanding How Excel Handles Formula Errors
Circular Reference
A circular reference does not result in an error
message that appears in the result cell. But Excel
does send up a few flags when it occurs.
Avoiding Common
Formula Errors
There are other errors you might make when
entering a formula, and those errors may not
result in an error message such as DIV/0! being
displayed in a cell. Here is a list of things you
should be careful of when typing a formula:
First off, a circular reference is caused when a
formula references itself. For example, suppose
you type =SUM(B2:B10) in cell B10. You are
telling Excel to add all the values in the range
B2:B10, including the total you are creating in
cell B10, which of course changes that total,
which changes the total again, and so on, in an
endless circular loop.
Make sure you type equals sign (=) at
the start of every formula. If you don’t,
Excel typically thinks that what you’ve
typed is text, and displays it in the cell
without calculating anything. For
example, if you type AVERAGE(B4:B7), you’ll
see that text, and not a result. Some
formulas, when you forget the equals
sign (=), are interpreted as a date: if you
type 3/8 instead of =3/8, you get March
8 instead of 0.375.
When you accidentally create a circular
reference, an error message appears on the status bar
similar to: “Circular Reference:F12”. If you open
a workbook that contains a circular reference,
you will see a warning telling you that the
workbook contains one or more circular references.
Don’t forget to match up all your
parentheses. If you forget a closing parenthesis,
Excel will warn you of the problem and
may attempt to “fix” your formula by
adding a closing parenthesis. This guess
may not be what you wanted. The only
way to know for sure is to carefully check
the formula and add the parenthesis
where it belongs.
If you need to enter a date in a formula,
make sure you use a four-digit year .
If you don’t, Excel may assume you mean
a year from the 20th century instead of
the 21st. For example, if you enter
=YEAR(“02/20/25”), Excel assumes
you mean 02/20/1925, rather than
Search JabSto ::

Custom Search