Page 90 - Excel Workbook for Dummies

10_798452 ch05.qxp 3/13/06 7:44 PM Page 73

Chapter 5

Building Formulas

In This Chapter
Constructing simple arithmetic and comparative formulas by hand
Constructing formulas that use Excel’s built-in functions
Editing formulas
Altering the order in which a formula’s operands are calculated
Creating external reference link formulas
Controlling formula recalculation

o one disputes that formulas are the center of almost every spreadsheet you create.
NBeing able to build formulas (both those that perform simple arithmetic calculations
as well as those that perform more sophisticated computations using Excel’s built-in func-
tions) is a critical skill. The exercises in this chapter give you a chance to practice building
both types of formulas as well as modifying how and when the formulas in the spreadsheet
are recalculated.

Building Formulas

All the formulas you build in an Excel spreadsheet regardless of their function and degree of
complexity have one thing in common: They all begin with one simple character, = (the
equal to sign).Typing an equal to sign activates the Insert Function, Enter, and Cancel but-
tons on the Formula bar. It also changes the nature of the Name Box drop-down box so that
its list displays commonly used functions rather than the range names assigned to the
workbook.

If you forget to type this as your initial character when creating formulas by hand (Excel is
always sure to put one in for you when you build formulas with the Insert Function button),
the program inserts the string of operands and operators you enter as a text reference.

If you build a legitimate formula, Excel either computes the answer and displays it in the
current cell in the worksheet or, if unable to successfully calculate the answer, the program
displays one of the following error values in the cell:

#NULL! appears when your formula specifies an intersection of two ranges that do not,
in fact, intersect.

#DIV/0! appears when your formula attempts to divide by zero.
#VALUE! appears when your formula contains some sort of improper argument type or
operand (such as a text entry when the operator requires a value).
#REF! appears when your formula contains an improper cell reference.