Using text in an IF statement?

[The Hun]

Using Excel 2002

This is what I want to do...
A1 = a number or text
A2 = a number
A3 = A1*A2

If A1 is text, what is the correct IF statement in A3 so I don't get
#Value as the answer?

Thanks,

 

[joeu2004]

A1  =  a number or text
A2  =  a number
A3  =  A1*A2
If A1 is text, what is the correct IF statement
in A3 so I don't get #Value as the answer?

Depends on what result you want when A1 is text. One way:

=if(istext(A1), "", A1*A2)

Another way:

=n(A1)*A2

If you would like a numerical result when A1 __looks__ like a number,
but is actually text, try:

=if(iserror(A1*A2), "", A1*A2)

 

[George Gee]

What DO you want as an answer if A1 is text?

Try in A3 =IF(ISNUMBER(A1),A1*A2,IF(ISTEXT(A1),""))

George Gee

 

[T. Valko]

It depends on what result you want when A1 is a text entry.

Start with this and we'll tweak it if needed:

=IF(COUNT(A1),A1*A2,"")

If A1 doesn't contain a number (that also means if A1 is empty) the formula
returns a blank.

 

[The Hun]

It depends on what result you want when A1 is a text entry.

Start with this and we'll tweak it if needed:

=IF(COUNT(A1),A1*A2,"")

If A1 doesn't contain a number (that also means if A1 is empty) the formula
returns a blank.

--
Biff
Microsoft Excel MVP

"The Hun" <[email protected]> wrote in message

Thank you. All three of you gave me workable solutions. The answer
to the question each of you asked -- "what result do you want?" -- is
either blank or 0 (zero), and I was able to produce both results from
the answers you gave.

NEW Question: What if I include A4 and A5?

If A4 or A5 is text (such as "no chrg"), what statement in A6 will add
the numbers that remain in A3, A4 or A5 and give me a numerical value
in A6?

A1 = a number or text
A2 = a number
A3 = A1*A2
A4 = a number or text
A5 = a number or text
A6 = TOTAL A3+A4+A5 (and get a numerical value)

Do I have to write nested IF statements in A4 and A5? If I do, I'm
not entirely sure how to do it.

Thanks,

 

[T. Valko]

Just use a SUM formula:

=SUM(A3:A5)

SUM ignores text.

If there are no numbers then the result will be 0. If you don't want to see
a 0 when no numbers are present then just use the same technique we've shown
you in the other formulas to return a blank instead of a 0.

 

[The Hun]

Thanks. Everything works now.

Just use a SUM formula:

=SUM(A3:A5)

SUM ignores text.

If there are no numbers then the result will be 0. If you don't want to see
a 0 when no numbers are present then just use the same technique we've shown
you in the other formulas to return a blank instead of a 0.

 

[T. Valko]

You're welcome. Thanks for the feedback!