Microsoft Office Tutorials and References
In Depth Information
Understanding How Excel Handles Formula Errors
Formulas that are inconsistent with
formulas in surrounding cells are
interpreted as a possible error. For example,
if A10 contains the formula =(A2–A8)/A9
and you enter =(B2–B8)/(B9*2.75) in B10
and =(C2–C8)/C9 in C10, Excel notices
the inconsistency and assumes cell B10
has an error because it’s different.
If you turn on worksheet protection
(which you’ll learn about in Chapter 13,
“Setting Security Options”), all cells in
the worksheet are typically protected
against changes. Still, you can selectively
“unprotect” the cells you want to allow
others to enter data into. Even so, if you
unprotect a cell with a formula, Excel will
see that as a possible error, because it’s
unusual that you would want to allow
anyone to change a formula in a
worksheet you’re protecting against unwanted
Formulas that omit adjacent cells may
also be flagged as errors. For example, if
you type the formula =AVERAGE(C3:C10)
in cell C14, it will be flagged as a possible
error if cells C11, C12, and C13 contain
data which perhaps should be included
in the formula.
Referring to cells on another worksheet
or in a different workbook within a
formula is a tricky business. For example,
=‘April’!H43–10 tells Excel to take the
value in cell H43 on the worksheet called
April, and subtract 10 from it. However,
if you forget to put the name of the
worksheet in single quotations (not
double-quotations), or if you leave out
the exclamation point (!) after the sheet
name, Excel will flag the formula as an
error because it won’t know what you’re
referring to. The simplest way to avoid
such errors is to click the cell you want,
even if that means switching workbooks
to click the right cell.
When using functions, make sure you
enter all required arguments and that
you refer to cells that contain the kind
of data the function is looking for. Also,
when entering a number as an argument,
do not include any formatting such as
commas (,) and dollar signs ($), as in
$12,387. You can, on the other hand, use
% to enter a percentage in a formula, as
in 2%.
Search JabSto ::

Custom Search