Page 163 - Excel Timesaving Techniques for Dummies

31_574272 ch27.qxd 10/1/04 10:49 PM Page 148
27 Smarter Formula
Construction

Technique

ormulas are the lifeblood of spreadsheets. When building new for-
Save Time By mulas, accuracy always trumps speed because few things can be as
Ftime-consuming as trying to clean up a bunch of errors that you’ve
Pointing to cell references
when building formulas spread everywhere when copying bad formulas. And by far the worst for-
mulas are not those that result in outright error values (the origins of
Using the Insert Function which can be tracked down and eliminated) but those that look fine even
feature though they aren’t accurate because they either refer to the wrong cells
Using labels instead or are performing the wrong calculations.
of cell references in
formulas To help you to avoid this kind of invisible but nevertheless detrimental
error, this technique covers ways you can work smarter in building for-
mulas. These tricks include the basic methods of pointing to formula cell
references rather than typing their ranges, relying on the Insert Function
feature to find and enter all but the most basic of Excel’s built-in functions,
and finally, using the row and column table headings in constructing mas-
ter formulas to ensure that they perform the intended calculations.

Pointing Out Cell References in Formulas

Over my many years as a software trainer, I wish I had a nickel for every
student I encountered who wanted to rush into completing a formula by
typing in the addresses of the cells and cell ranges used in a new formula.
(If I did, I probably would have retired long ago, and I wouldn’t be writing
this book.) I must say that I’ve had to work to convince some of the effi-
cacy of always adding cell references to formulas by selecting their cells
directly in the worksheet. These are usually those students who, because
they’re whiz-bang typists, don’t want to take the time to point to the
addresses that they can type into the formula so much quicker.
The problem with these folks typing in the cell addresses is that they can
easily type in the wrong addresses, and if this doesn’t result in an outra-
geous computation or an outright error value, they can speed on their
way not realizing that their formula is referring to the wrong cells and that
they’re results are worthless or, worse, harmful. The chances of this kind
of thing happening when you point out the cells or cell range by taking
the time to select them is miniscule by comparison.