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

changes.

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%.