1.

Start Excel 2003 and open the workbook named Practice 1 in your Excel 2003 Practice folder.

You will see right away that there are #DIV/0 errors in cells B9 and G2. You can click the small green triangles in the upper left corner of the cells to display the error smart tag and get more information.

Choose the Show Formula Auditing Toolbar from the error smart tag drop menu, or choose View->Toolbars and place a check next to the Formula Auditing option.

2.

Make sure that Cell B9 is the active cell and click the trace Precedents button on the auditing toolbar.

You will see a red arrow pointing from cell B9 to cell G2. This red arrow indicates that the cell that B9 is getting information from (G2) also has an error. If you click the Trace precedents button again, Excel will display more arrows indicating the cells that feed G2.

You can see in the formula bar that the active cell, B9, contains an average of the cells G2:G5.

You can also see from the blue arrows that cell G2 is fed from, (contains references to) E2 and F2.

The error in B9 and G2 is division by zero, and F2, one of the feeder cells to G2, has a value of zero. The next step in correcting this error is to investigate the relationship between cell G2 and F2.

Note: The chain of arrows linking B9 to G2 to F2 would be displayed if you clicked the Trace Error button on the auditing toolbar.

3.

Click the remove all arrows button on the auditing toolbar, and make cell F2 the active cell.

You will see by looking in the formula bar, that cell F2 contains the formula =B2*C2*D2

Because the zero in F2 is the result of a formula, we cannot just enter a non-zero number in F2 to fix the problem. Notice that cell C2, one of the references in the formula, also contains a zero.

4.

Make C2 the active cell, and click the trace dependents button on the formula auditing toolbar three times.

You can now see that Cell F2 is dependent on cell C2, and that cell G2 is indirectly dependent on C2 (through its dependence on F2). Cell B9 is also dependent on C2 through G2 and F2.

Also, by looking in the formula bar, you can see that C2 contains only a number, not a formula.

It looks as if C2 could be the root of the problem (a box with zero height doesn’t make much sense).

5.

Change the value in cell C2 to a non-zero number (lets say 6) and see what happens.

Now the error messages are gone, and everything is working properly. Cell C2 was the source of the error.

A zero in cell C2 makes the formula in F2 evaluate to zero (B2*0*D2=0). The result of this formula is used to calculate the density in cell G2, (density = mass/volume). Because the volume is 0, a division by zero error occurs in cell G2 which is passed on to cell B9.

6.

Save your work and exit Excel.