Page 330 - Excel Workbook for Dummies

35_798452 ch24.qxp 3/13/06 7:47 PM Page 313
Chapter 24: Top Ten Tips for Using Excel like a Pro 313
Trap Error Values in Their Original Formulas

Error values are not only unsightly but, because they spread so easily to other depend-
ent formulas throughout the spreadsheet, they can be difficult to eradicate. The most
professional spreadsheet designers, therefore, take steps whenever possible to prevent
the spread of error values across the spreadsheet by trapping them at their source.
The most famous example of where this kind of error trapping is needed occurs in divi-
sion formulas where the divisor is a cell reference that at times can be blank. As you
remember, blank cells as well as cells with text entries carry a zero (0) value as far as
formula computations are concerned so that if they serve as the divisor in a division
calculation in a formula, that formula returns the #DIV/0! error value to the cell.
As you found out if you performed Exercise 12-4 in Chapter 12, you can prevent Excel
from returning this or any other kind of error value (including #NA) to the cell by
making the division calculation itself the value_if_false argument of an IF function that
uses the ISERROR logical function in its logical_test argument to test for the return of
an error value, and the value 0 (zero) as its value_if_true argument.

So, for example, if cell K7 is the divisor of a division computation and it could be blank
at times and therefore cause the division calculation to return #DIV/0!, you could pre-
vent this and block its spread to other formulas dependent upon its result with the
addition of the following IF function:

=IF(ISERROR(B7/$K$7),0,B7/$K$7)
In this case, Excel returns zero (0) to the cell in place of any error value, performing
the actual division only when it’s safe to do so.
You can then use Conditional Formatting (see Chapter 2) to flag all error-trapping for-
mulas that return zero (0) — instead of the desired calculated result — with a special
type of color or font. Then, you can use the Find feature (see Chapter 4) to locate all
the cells in the worksheet that now sport this special formatting when it comes time to
fix their cell references so that their formulas return values other than zero.

Save Memory by Using Array Formulas

In Exercise 6-5 in Chapter 6, you got some experience with constructing array formulas.
As you may recall, an array formula is one that constructs the same type of calculation
in an entire range of cells in a data table at the time you create it. As a result, you don’t
go through the normal process of first constructing a master formula and then copying
to all the other cells in the table that need to perform the same type of calculation.

Array formulas offer two distinct advantages over normal formulas:

Array formulas enable you to create all the duplicate formulas needed in the
table in a single operation
Array formulas require a lot less computer memory to store than individual
copies of a single master formula

It is actually the latter, memory-related benefit that makes array formulas so useful.
By building array formulas, you can save substantial amounts of computer memory
(remember that Excel must be able to load every bit of data in a workbook into
memory in order to be able to open the file at all — it does not perform like other
programs that can load just parts of a document into memory, switching them out