Page 94 - Excel Workbook for Dummies

10_798452 ch05.qxp 3/13/06 7:44 PM Page 77
Chapter 5: Building Formulas 77

2. Build the following comparative formulas in the designated cells:
• In cell D5, a formula that compares the values in cell A5 to that in cell B5
and indicates whether the one in A5 is larger than the one in B5 — use the
> (greater than) symbol as the operator.
• In cell D6, a formula that compares the values in cell A6 to that in cell B6
and indicates whether the one in A6 is smaller than the one in B6 — use the
< (less than) symbol as the operator.
• In cell D8, a formula that compares the values in cell A8 to that in cell B8
and indicates whether the one in A8 is unequal to the one in B8 — use the
<> (less than and greater than back to back) symbols as the operator.
3. Change the value in the source cell, A2, from 128 to 34.
Note how Excel immediately updates all the formulas in columns A, C, and D
whose computations depend in some way on this value.
4. Save your changes to your Practice Formulas.xls workbook before proceeding on
to Exercise 5-3.

Well-designed spreadsheets contain many dependent formulas like your Practice
Formulas.xls workbook. The problem comes when any the formulas on which they
depend return one of those dreaded error values covered earlier in this chapter.
When that happens, the error values spread like wildfire to all the dependent formu-
las, making it very difficult, if not impossible, to identify the source of the problem.

Try It

Exercise 5-3: Working with Error Values in Formulas
Use your Practice Formulas.xls workbook to get some experience with the error values
that formulas can return and how they infect all dependent formulas in a spreadsheet:

1. Open the Define Name dialog box (Insert➪Name➪Define) and then click Source
in the list box before you select Delete and OK.

As soon as you click OK to close the Define Name dialog box, most of the cells in
this spreadsheet contain the #NAME? error value because every formula you cre-
ated in this worksheet is dependent in some way upon the source range name,
inheriting this error value from the original formula in cell B4 with the external
reference the moment you delete its name.
2. Use Undo to restore the source range name to the workbook and get rid of those
awful #NAME? error values in the spreadsheet.
3. Now, delete the value (2.2) in cell B6 and see what effect this has on the division
formula in cell C6.
As soon as you empty cell B6, a #DIV/0! error value is returned by the division
formula in cell C6. This is because all empty cells in any Excel worksheet carry
the value of zero, and this is why #DIV/0! is the most common type of error value
to plague spreadsheets. Often, a template or new spreadsheet you create con-
tains division formulas referring to empty cells for which you do not have or
have not yet entered values. These all naturally return #DIV/0! error values (in
Chapter 12, you find out how to create formulas using the IF function that pre-
vent such formulas from returning this type of error value).
4. Use Undo to restore the original 2.2 value to cell B6 and remove the #DIV/0! error
value from cell C6; in cell B7, enter the text ten percent to see what effect this
has on the multiplication formula entered in cell C7.