| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#REF or #N/A with "IF" formula
FLKulchar
In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#REF or #N/A with "IF" formula
Look in HELP under ERROR.TYPE function.
If an error occurs =ERROR.TYPE(A1) for #N/A = 7, and for #REF = 4 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "F. Lawrence Kulchar" wrote: FLKulchar In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#REF or #N/A with "IF" formula
Try
=IF(ISNA(A1), 3,4) Mike "F. Lawrence Kulchar" wrote: FLKulchar In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#REF or #N/A with "IF" formula
That would be OK if you were looking for the text string "N/A", but if you
are looking for the error condition #N/A then the formula you need is =IF(ISNA(A1), 3,4) If you wanted to test for the #REF error, then you could use =IF(ISERR(A1),IF(ERROR.TYPE(A1)=4, 3,4),4) -- David Biddulph "F. Lawrence Kulchar" wrote in message ... FLKulchar In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#REF or #N/A with "IF" formula
It depends on what exactly you want.
If you want to test A1 for *any error*: =IF(ISERROR(A1),3,4) If you want to test for #N/A: =IF(ISNA(A1),3,4) If you want to test for #REF!: =IF(ISNA((ERROR.TYPE(A1)=4)),4,3) -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... FLKulchar In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#REF or #N/A with "IF" formula
=IF(ISNA((ERROR.TYPE(A1)=4)),4,3) will of course return 3 for #N/A (or for
other errors such as !DIV/0!), as well as for #REF!. -- David Biddulph "T. Valko" wrote in message ... It depends on what exactly you want. If you want to test A1 for *any error*: =IF(ISERROR(A1),3,4) If you want to test for #N/A: =IF(ISNA(A1),3,4) If you want to test for #REF!: =IF(ISNA((ERROR.TYPE(A1)=4)),4,3) -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... FLKulchar In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#REF or #N/A with "IF" formula
Ooops!
Yeah, you're right. Disregard that formula. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =IF(ISNA((ERROR.TYPE(A1)=4)),4,3) will of course return 3 for #N/A (or for other errors such as !DIV/0!), as well as for #REF!. -- David Biddulph "T. Valko" wrote in message ... It depends on what exactly you want. If you want to test A1 for *any error*: =IF(ISERROR(A1),3,4) If you want to test for #N/A: =IF(ISNA(A1),3,4) If you want to test for #REF!: =IF(ISNA((ERROR.TYPE(A1)=4)),4,3) -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... FLKulchar In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#REF or #N/A with "IF" formula
Hi,
you asked "how come...." The reason is because "N/A" which you enterd is a text entry and what Excel is returning when you see #N/A is an error value, not text. This is true for all 7 errors that Excel displays such as NAME?, VALUE# and #REF! You can trap these types of errors, depending on which one, using ISERR ISNA ISERROR and you can determine which one it is by using ERROR.TYPE You should look each of these up in the Help system to understand what the differences are and what the return value is from the last one. -- Thanks, Shane Devenshire "F. Lawrence Kulchar" wrote: FLKulchar In cell A1, I have either #N/A or #REF because, there 'was' an error in my formula! How come...=IF(A1="N/A", 3,4) does NOT work? How can I make it to work...in other words, if cell A1 has a #N/A in it, how can I make the 'IF' formula work??? Same is true for the "#REF" error!! Please help. FLKulchar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
Hybrid Mode
