Formula to rid of DIV/o!

[Frank Kabel]

...

Always better to look for causes then to try trial & error treating the
symptoms. If K2-J2 returns an error, then what does that imply are the
contents of K2 or J2 or both? Either one or the other or both are text or
error values themselves. If K2 were ="" or anything returning the same
value, =IF(K2,1,0) would return a #VALUE! error (try it!). For that matter,
if K2 were either ="1" or ="0" or equivalents, the previous IF formula would
still return #VALUE! (try it!). AND(K2,AnythingElseYouCouldThinkOf) would
also return a #VALUE! error. Excel just doesn't like text, even numeric
text, in boolean contexts.

Hi Harlan
thanks for this. Thought I'd tested it but only with real blanks. So
thanks for the correction.

If the OP's formula above is returning errors, then the formula the OP needs
is either

=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

=SUMPRODUCT(J2:K2,{-1,1})

This is a nice one (though it will not return "" in case of J2:K2 are
blank -> which seems to be the OP's desired result).

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

Frank

 

[hgrove]

Frank Kabel wrote...

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 exclud
true numeric zeros as opposed to blanks or nonnumeric text. K2-J
*can't* throw off errors unless K2 or J2 contains an error or evaluate
to nonnumeric text. If either is numeric zero, there's no error, so it'
highly likely the OP is confusing #DIV/0! errors from dividing by zero
which does need to be trapped, and #VALUE! errors when subtractin
numbers, which does not need zero trapping but nonnumber trapping.

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

 

[Frank Kabel]

..

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

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