# #DIV/O! or #N/A problems

#### audrey

##### Active Member
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'

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### SteveO59L

##### Well-known Member
=if(iserror(a1/b1),0,a1/b1)

#### Domski

##### Well-known Member
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

##### Active Member
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

##### Well-known Member

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

##### Active Member
I still get div problem when 0 divided to 0

#### Domski

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

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

Dom

Replies
3
Views
140
Replies
23
Views
741
Replies
2
Views
272
Replies
6
Views
165
Replies
1
Views
108

### Forum statistics

1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back