Formula to rid of DIV/o!

Frank Kabel wrote...
=IF(COUNTIF($J2:$K2,"0")=2,$K2-$J2,"")

But this formula definetely won't help if K2 contains for example
="". It will also return an error. Of course not on the condition
part but on the TRUE part of the If statement
...

Me bad, didn't test. This could be done with an array formula

=IF(COUNT(1/$J2:$K2)=2,$K2-$J2,"")

So to add an alternative I'd use:
=IF(AND(N(J2),N(K2)),K2-J2,"")

OK, but there just doesn't seem any point to the OP wanting exclude
true numeric zeros as opposed to blanks or nonnumeric text. K2-J2
*can't* throw off errors unless K2 or J2 contains an error or evaluates
to nonnumeric text. If either is numeric zero, there's no error, so it's
highly likely the OP is confusing #DIV/0! errors from dividing by zero,
which does need to be trapped, and #VALUE! errors when subtracting
numbers, which does not need zero trapping but nonnumber trapping.

So we're both still dealing with the symptoms when the cause is that
either cell K2 or cell J2 contains something it oughtn't.


---
Message posted from http://www.ExcelForum.com/

..

Me bad, didn't test. This could be done with an array formula

This happens so rarely for your formula suggestions :-)


=IF(COUNT(1/$J2:$K2)=2,$K2-$J2,"")

nice idea


So to add an alternative I'd use:
=IF(AND(N(J2),N(K2)),K2-J2,"")

OK, but there just doesn't seem any point to the OP wanting exclude
true numeric zeros as opposed to blanks or nonnumeric text.

You're right. The non-array approach would then probably require
ISNUMBER function calls instead of N() function calls.


So we're both still dealing with the symptoms when the cause is that
either cell K2 or cell J2 contains something it oughtn't.

Ack, but maybe the OP will come back.

Regards
Frank