Avoid div/0 error?

[MsCynic]

Hi guys, when dragging a cell down to repeat a formula throughout a column, what is the trick that avoids having the #DIV/0! error display on rows without data in them?

Thanks in advance,
C

 

[srizki]

Hi every one,
As Bruno said, I should not add anything after Aladin and Hotpepper, I am just adding to see if I am correct,
I used a formula that was given to me by Aladin, with a little change.
=IF(COUNT(D136,P136)=2,(D136-P136)/ABS(P136),"").
The reason is that if OP has divisor (denominator) and numerator is blank?
Please correct me, if I am wrong so I can learn more.
Thanks

 

[Scott Huish]

That will do the division if there is a number in D136 and P136. However, it won't protect against #DIV/0! if there is an actual 0 in P136 because 0 is a number. However, the last formula Aladin posted should.

 

[MsCynic]

Hi again, the formulae suggested all work fine when there is a figure in P136 and no figure in D136. It gives me the return of -100%.

However, what about when there is a figure in D136 and none in P136? How can I have the formula display 100% in this case?

 

[Giordano Bruno]

Hi MsCynic,

My formula would just need an extra condition.

=((P138<>0)*(D138-P138))/ABS(((P138=0)+P138))+(P138=0)

It's getting longer and longer. It's only advantage is that it doesn't use the "If" function, which I try not to overuse for aethetic reasons. If you wish to use the "If" solutions, just change the "" to 1.

 

[mikerickson]

I just use condidional formatting
[ formula =(ISERROR(a1)) ]

to set the font color to white.

 

[srizki]

My formula works fine in this situation, but also read Hotpepper's reply, you can change it from "" to "100%".
I hope this works.

 

[MsCynic]

Thanks again everyone.
C

 

[mypig7]

#DIV/0!

In a cell I have a formula dividing 1 cell by another e.g. =a1/b1.
If a1 is blank #DIV/0! appears.

How can I create a formula (nto a macro) so that #DIV/0! is replaced by an empty cell in this case.

Cheers

 

[Giordano Bruno]

You cannot have a formula turn into an empty cell without using a macro.

 

[Aladin Akyurek]

Re: #DIV/0!

In a cell I have a formula dividing 1 cell by another e.g. =a1/b1.
If a1 is blank #DIV/0! appears.

How can I create a formula (nto a macro) so that #DIV/0! is replaced by an empty cell in this case.

Cheers

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