Text in Formula

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

I have this formula:

=IF(D13="","",IF(C13=D13,"",IF(C13<0,D13-C13)))

The problem is that sometimes C13 and/or D13 will be text instead of a
number. The text might say E or PK which is actually the same as
zero.

I can't figure out a way to rewrite the formula to allow for this.
 
JimS said:
=IF(D13="","",IF(C13=D13,"",IF(C13<0,D13-C13)))

The problem is that sometimes C13 and/or D13 will be text instead of a
number.  The text might say E or PK which is actually the same as
zero.
Click to expand...
....

More alternatives. If any text should be treated as 0,

=IF(OR(D13="",C13=D13),"",N(D13)-N(C13))

If only certain text strings should be treated as 0 while others
should throw errors,

=IF(OR(D13="",C13=D13),"",IF(COUNT(MATCH(D13,{"E";"PK"},0)),0,D13)-
IF(COUNT(MATCH(C13,{"E";"PK"},0)),0,C13))
 
Hi,

This seems to do what you want:

=IF(OR(D13="",C13=D13,N(C13)>=0),"",N(D13)-C13)
 
Thanks to all. I will look at all of these suggestions. I have one
question. What is "N" in the formula below?

Thanks again
 
I had one more formula to change.

I had to change the top formula to the bottom formula. It seems to
work. Did I do it right?

=IF(B12="","",IF(B11="",IF(D12>=0,D13*-1+I12,IF(D12="",D13*-1+I12))))

=IF(B12="","",IF(B11="",IF(D12>=0,N(D13)*-1+I12,IF(D12="",N(D13)*-1+I12))))
 
Did I do it right?

Yeah, it'll work like that but I'd tweak it a little:

=IF(OR(B12="",B11=""),"",IF(D12>=0,-N(D13)+I12,IF(D12="",-N(D13)+I12,"")))
 
Back
Top