Conditional Formatting for #DIV/0!

[Igorin]

Hello,

Does anyone how to do the following?

I have several columns that contain a division formula that make reference
to other cells.

Some cells will not not always contain data. Because of this I'm getting a
#DIV/0! error (which is totally normal and to be expected).

What I wish to do is to make the #DIV/0! disappear using conditional
formatting, for example, 'If "=#DIV/0!" then use font color white'.

Any ideas how this can be done? Maybe there is another way to achieve this
without using conditional formatting.

I'm using Excel 2003.

Thanks for the help.

 

[TomPl]

Try this:

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

 

[Igorin]

Thank you very much!

 

[dfunk]

try this one too.

=if(iserror(a1/b1),"",a1/b1)

Thank you very much!

 

[ShaneDevenshire]

Hi,

The formula solutions are considered better solutions than using conditional
formatting, but if you really wanted to use formatting assume that the top
left corner of your range, which you select, is A1:

1. Choose Format, Cells, and from the first drop down pick Formula is
2. In the next box enter the formula:
=ISERR(A1)
3. Click the Format button and on the Font tab pick the Color White. Click
OK twice.

So in the spreadsheet, considering the formulas you have already recieved,
you could also use
=IF(B1,A1/B1,"")

 

[Igorin]

Thank you, Shane!

--
igor

Hi,

The formula solutions are considered better solutions than using conditional
formatting, but if you really wanted to use formatting assume that the top
left corner of your range, which you select, is A1:

1. Choose Format, Cells, and from the first drop down pick Formula is
2. In the next box enter the formula:
=ISERR(A1)
3. Click the Format button and on the Font tab pick the Color White. Click
OK twice.

So in the spreadsheet, considering the formulas you have already recieved,
you could also use
=IF(B1,A1/B1,"")

 

[Igorin]

Great! Thanks a lot!

--
igor

try this one too.

=if(iserror(a1/b1),"",a1/b1)