A lot of the data i deal with involves counting and dividing. A percentage
may be presented in the worksheet. Instead of #DIV/0!, I would like for N/A,
not available, to appear in the cell. How do i create a formula of sorts that
will display the average, for example, or N/A if there is division by 0.

This will help me a lot because i won't have to look over hundreds of cells
to spot all the divison by zero errors and i don't have to worry about
recalculations not appearing because formulas have been deleted to put N/A in
the cells

Thanks very much!

If the dividend and the divisor are in cells A1 and B1:

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

--

Vasant


"jacob" wrote in message
...
A lot of the data i deal with involves counting and dividing. A percentage
may be presented in the worksheet. Instead of #DIV/0!, I would like for
N/A,
not available, to appear in the cell. How do i create a formula of sorts
that
will display the average, for example, or N/A if there is division by 0.

This will help me a lot because i won't have to look over hundreds of
cells
to spot all the divison by zero errors and i don't have to worry about
recalculations not appearing because formulas have been deleted to put N/A
in
the cells

Thanks very much!

Assume the div error is because B2 is empty in =A2/B2

Then you could use =IF(B2=0,NA(),A2/B2)

or

=IF(B2=0,"",A2/B2)

--
Regards,

Peo Sjoblom


"jacob" wrote in message
...
A lot of the data i deal with involves counting and dividing. A percentage
may be presented in the worksheet. Instead of #DIV/0!, I would like for
N/A,
not available, to appear in the cell. How do i create a formula of sorts
that
will display the average, for example, or N/A if there is division by 0.

This will help me a lot because i won't have to look over hundreds of
cells
to spot all the divison by zero errors and i don't have to worry about
recalculations not appearing because formulas have been deleted to put N/A
in
the cells

Thanks very much!

Hi Peo

The () after NA in your formula produces a #N/A answer for each zero in
columnB.
Has the # any purpose as apposed to just N/A?

TIA

Aussie Bob C.

"Peo Sjoblom" wrote:

Assume the div error is because B2 is empty in =A2/B2

Then you could use =IF(B2=0,NA(),A2/B2)

or

=IF(B2=0,"",A2/B2)

--
Regards,

Peo Sjoblom


"jacob" wrote in message
...
A lot of the data i deal with involves counting and dividing. A percentage
may be presented in the worksheet. Instead of #DIV/0!, I would like for
N/A,
not available, to appear in the cell. How do i create a formula of sorts
that
will display the average, for example, or N/A if there is division by 0.

This will help me a lot because i won't have to look over hundreds of
cells
to spot all the divison by zero errors and i don't have to worry about
recalculations not appearing because formulas have been deleted to put N/A
in
the cells

Thanks very much!

It returns a true error as opposed to a text version which would be "N/A"

If you are doing average calculations you should go for the text version
since average ignores text and has to be changed to cope with errors


--
Regards,

Peo Sjoblom


"Robert Christie" wrote in message
...
Hi Peo

The () after NA in your formula produces a #N/A answer for each zero in
columnB.
Has the # any purpose as apposed to just N/A?

TIA

Aussie Bob C.

"Peo Sjoblom" wrote:

Assume the div error is because B2 is empty in =A2/B2

Then you could use =IF(B2=0,NA(),A2/B2)

or

=IF(B2=0,"",A2/B2)

--
Regards,

Peo Sjoblom


"jacob" wrote in message
...
A lot of the data i deal with involves counting and dividing. A
percentage
may be presented in the worksheet. Instead of #DIV/0!, I would like
for
N/A,
not available, to appear in the cell. How do i create a formula of
sorts
that
will display the average, for example, or N/A if there is division by
0.

This will help me a lot because i won't have to look over hundreds of
cells
to spot all the divison by zero errors and i don't have to worry about
recalculations not appearing because formulas have been deleted to put
N/A
in
the cells

Thanks very much!