#DIV/0

[amag]

IS it possible to 'conditional format' or some other way (not macros)
such that if I have #DIV/0 anywhere in the sheet it should instead give
NA.

TIA/Amag

 

[Pete_UK]

If your formula is something like =A1/B1, you can change this to:

=IF(B1=0,0,A1/B1)

It is often better to return 0 or "" rather than "NA", as you might
want to use the result in some other formula. Generally, you can trap
errors as follows:

=IF(ISERROR(your_formula),0,your_formula)

or use "" instead of 0 if you want to display a blank.

Hope this helps.

Pete

 

[amag]

Tks for ur prompt reply ...i tried if formula ... works ...but somehow
i need to check the complete sheet and i have some 20 sheets in the
workbook...i can individually goto sheets and repeat the process 20
times over .... but can this formula be used somehow once to check the
sheet completely?
Tks again/Amag

 

[Pete_UK]

You only need a formula like this:

=IF(B1=0,0,A1/B1)

to avoid the division by zero error if you have a formula where some
value is divided by another. There is no point in applying it to a
formula like = A1 * B1 because you will never get a #DIV0 error from
that formula.

The more general error-trapping formula can be used where you can
identify that errors might occur, but I don't think it is necessary to
apply it to every formula in your workbook.

So, the short answer is No.

Pete