# If function ignoring NA

#### Rookieeee

##### New Member
Hello guys,

I have a column of numbers and some NA. I am creating dummy variables, for example, IF(A2>0.7,1,0) and repeat this function into the entire column. However, I have NA in this column. Therefore, how can I ignore NA and set the output of NA as 'NO'.

Thank you so much.

#### Rick Rothstein

##### MrExcel MVP
I have a column of numbers and some NA. I am creating dummy variables, for example, IF(A2>0.7,1,0) and repeat this function into the entire column. However, I have NA in this column. Therefore, how can I ignore NA and set the output of NA as 'NO'.
Maybe....

=IFERROR(IF(A2>0.7,1,0),"NO")

or possibly...

=IF(A2=NA(),"NO",IF(A2>0.7,1,0))

Last edited:

#### Rookieeee

##### New Member
Maybe....

=IFERROR(IF(A2>0.7,1,0),"NO")

or possibly...

=IF(A2=NA(),"NO",IF(A2>0.7,1,0))
The first on works, thank you so much

#### Peter_SSs

##### MrExcel MVP, Moderator
or possibly...

=IF(A2=NA(),"NO",IF(A2>0.7,1,0))
For me, that returns #N/A for all values in A2 except errors other than the #N/A error

If we only want "NO" returned for #N/A errors and not other errors then for your version (I think 2010?) you would need
=IF(ISNA(A2),"NO",IF(A2>0.7,1,0))

or for subsequent versions the If(ISNA .. has been compressed into a single function
=IFNA(IF(A2>0.7,1,0),"NO")
@Rookieeee
Also note that in any of the suggeted formulas IF(A2>0.7,1,0) can be replaced by --(A2>0.7)
For example, the formula you chose can also be written as
=IFERROR(--(A2>0.7),"NO")

#### Rookieeee

##### New Member
For me, that returns #N/A for all values in A2 except errors other than the #N/A error

If we only want "NO" returned for #N/A errors and not other errors then for your version (I think 2010?) you would need
=IF(ISNA(A2),"NO",IF(A2>0.7,1,0))

or for subsequent versions the If(ISNA .. has been compressed into a single function
=IFNA(IF(A2>0.7,1,0),"NO")

@Rookieeee
Also note that in any of the suggeted formulas IF(A2>0.7,1,0) can be replaced by --(A2>0.7)
For example, the formula you chose can also be written as
=IFERROR(--(A2>0.7),"NO")
Wow, Amazing. Thank you for your help!!!

#### Peter_SSs

##### MrExcel MVP, Moderator
Wow, Amazing. Thank you for your help!!!
You're very welcome. Thanks for the follow-up.

1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...