#DIV/0! Error


Posted by Greg on February 06, 2001 3:53 PM

I read this and while the sheet is active the error
is no longer visible....however when then the sheet
is printed the error is still visible...
anyone know how the error when printing can be hidden.
You may not want the results of a formula to be
displayed in your worksheet when the formula
calculation results in an error value. In Excel,
you can hide error values by using conditional
formatting or conditional formulas
In the following example, the error value #DIV/0!
is the result of a formula in cell D1 that tries to
divide the value in cell C1 by the value in cell B1,
which is empty:

You could delete the formula from the cell, of course.
But, you want to keep it so that it calculates and
displays valid results when a value is entered in B1.
You can do this with conditional formatting in
Excel 2000. Just follow these steps:

1. Select the cell or cells that contain formulas
that may calculate error results that you don't want to display.

2. On the Format menu, click Conditional Formatting.

3. In the Conditional Formatting dialog box,
click the Condition 1 list and click Formula Is.

4. In the box to the right of the Condition 1 list,
enter the following formula:

=ISERROR(cell_reference)
where cell reference is the relative reference of
the active cell in the selection.
In the example shown, the active cell in the selection
is D1. When you select a range of cells to
conditionally format, the formula must evaluate
each cell in the range. However, when you enter only
the relative reference of the active cell in the
selection, Excel adjusts the references to the other
cells relative to the active cell.

5. Click the Format button. In the Format Cells
dialog box, click the white color in the Color list.

6. Click OK in the Format Cells dialog box,
and then click OK in the Conditional Formatting
dialog box.


Posted by Dave Hawley on February 06, 2001 4:11 PM


Hi Greg

You may be better off using the error function within your formulas, like:


=IF(ISERROR(A1/A2),"",A1/A2)


Dave

Hi
OzGrid Business Applications

Posted by Mark W. on February 06, 2001 4:47 PM

=IF(A2,A1/A2,"")

Posted by Dave Hawley on February 06, 2001 4:51 PM


All that does is replace #DIV/0! with FALSE.


OzGrid Business Applications

Posted by Mark W. on February 06, 2001 5:04 PM

Not on my worksheet! You've missed the point. There's no need to evaluate the ratio and then test for an error condition when all that's needed to prevent #DIV/0! is check the denominator.



Posted by Dave Hawley on February 06, 2001 11:48 PM


Sorry mark, I thought I had stopped that before it went through. That's what happens when you pull an all nighter :-)

Dave

OzGrid Business Applications