| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Using text in an IF statement?
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, |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Using text in an IF statement?
On Jun 12, 9:44*pm, The Hun wrote:
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) |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Using text in an IF statement?
What DO you want as an answer if A1 is text?
Try in A3 =IF(ISNUMBER(A1),A1*A2,IF(ISTEXT(A1),"")) George Gee "The Hun" wrote in message ... 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, |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Using text in an IF statement?
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" wrote in message ... 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, |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Using text in an IF statement?
On Jun 12, 10:50 pm, "T. Valko" wrote:
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" 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, |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Using text in an IF statement?
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. -- Biff Microsoft Excel MVP "The Hun" wrote in message ... On Jun 12, 10:50 pm, "T. Valko" wrote: 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" 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, |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Using text in an IF statement?
Thanks. Everything works now.
On Jun 13, 9:59 am, "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "The Hun" wrote in message ... On Jun 12, 10:50 pm, "T. Valko" wrote: 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" 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, |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Using text in an IF statement?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "The Hun" wrote in message ... Thanks. Everything works now. On Jun 13, 9:59 am, "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "The Hun" wrote in message ... On Jun 12, 10:50 pm, "T. Valko" wrote: 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" 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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statement with text | Excel Worksheet Functions | |||
| If Statement for Text | Excel Discussion (Misc queries) | |||
| If Statement - With Formated text | Excel Worksheet Functions | |||
| if statement with text | Excel Discussion (Misc queries) | |||
| If statement for text | Excel Worksheet Functions | |||
Linear Mode
