Text in Formula

[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.

 

[T. Valko]

Try this:

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

 

[Bernard Liengme]

Try
=IF(and(ISNUMERIC(D13),ISNUMERIC(C13)),IF(C13<0,D13-C13,""),"")
best wishes

 

[Harlan Grove]

=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.

....

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))

 

[ShaneDevenshire]

Hi,

This seems to do what you want:

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

 

[JimS]

Thanks to all. I will look at all of these suggestions. I have one
question. What is "N" in the formula below?

Thanks again

 

[JimS]

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))))

 

[Max]

.. What is "N" in the formula ..
It's a function to convert text to zeros here
(Check up Excel's help for more info on it)

Your orig. post did mention:

The problem is that sometimes C13 and/or D13 will be text

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---

 

[T. Valko]

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,"")))