#DIV/O! or #N/A problems

[audrey]

Hello,

it might be a simple question for most of you but, when I try to retrive data I sometimes get the following errors like

#DIV/O! or #N/A

those are apparering in case like

=HPVAL(F10, B94, F9, F7, F8) or even simplier cases like 0 minus 0 !!!

is there a way to ignore them automatically from options or an if function so that if data cannot be calculated or retrived simply fill the cell with '0'

 

[SteveO59L]

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

 

[Domski]

For #N/A you can use this:

=IF(ISNA(your_formula),"",your_formula)

You can also use the more wide ranging:

=IF(ISERROR(your_formula),"",your_formula)

Be a little careful using ISERROR though as it will mask all errors in your formula.

If you have 2007 you should also look at the IFERROR function.

Dom

 

[audrey]

thanks guys


@Dom

If I understood correctly, in case of an error is this formula leaves that cell blank ?

=IF(ISNA(your_formula),"",your_formula)

and in this case

=IF(ISNA(your_formula),"0",your_formula)

fills with zero ?

 

[Domski]

Nearly...

=IF(ISNA(your_formula),"0",your_formula)

...will put 0 in but it will be a text string. For a numeric value to be returned use:

=IF(ISNA(your_formula),0,your_formula)

Dom

 

[audrey]

I still get div problem when 0 divided to 0

 

[Domski]

For #DIV/0! you really just need to test that the divisor isn't 0 like this:

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

Dom