If function ignoring NA

Rookieeee

New Member
Joined
Aug 26, 2019
Messages
22
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
Joined
Apr 18, 2011
Messages
35,501
Office Version
2010
Platform
Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,177
Office Version
365
Platform
Windows
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
Joined
Aug 26, 2019
Messages
22
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!!!
 

Forum statistics

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

Some videos you may like

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...
Top