If function ignoring NA

[Rookieeee]

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]

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

 

[Rookieeee]

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]

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]

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]

Wow, Amazing. Thank you for your help!!!

You're very welcome. Thanks for the follow-up.