# Thread: IF Function ignoring NA Thanks: 0 Likes: 0

1. ## IF Function ignoring NA

Hello guys,

I am currently processing a huge dataset. My question is that how can I ignore NA when I want to run and repeat this function in excel.
Code:
`=IF(AND(RankLN!D45>0.3,RankLN!D45<=0.7),"M",IF(RankLN!D45>0.7,"W","L"))`
In detail, for example, I have a column, we say column D. In this column, I have huge number of values between 0-1 and some NA, what I need to do is to rank and divide 0-1 using 'W', 'M', 'L'. However, in this column I have NA, so how can I ignore NA?

Thank you so much

2. ## Re: IF Function ignoring NA

Maybe this

Code:
`=IF(RankLN!D45="NA","",IF(AND(RankLN!D45>0.3,RankLN!D45<=0.7),"M",IF(RankLN!D45>0.7,"W","L")))`

3. ## Re: IF Function ignoring NA

Originally Posted by Michael M
Maybe this

Code:
`=IF(RankLN!D45="NA","",IF(AND(RankLN!D45>0.3,RankLN!D45<=0.7),"M",IF(RankLN!D45>0.7,"W","L")))`
It works, Thank you so much.

Cheers,

4. ## Re: IF Function ignoring NA

Originally Posted by Rookieeee
It works,
In that case your "NA" values must be text as opposed to actual error values like in your other thread with the same title?

BTW, for the formula being discussed in this thread,
- what is the name of the worksheet that it is on?
- are there any values in column D actually equal to 0 and/or 1 or are they all "between" as you described in post 1 here?

5. ## Re: IF Function ignoring NA

Another way that you could do it.

=IFERROR(INDEX({"W","M","L"},MATCH(RankLN!D45,{1,0.7,0.3},-1)),"")

or possibly (decimal precision might need adjusting).

=IFERROR(LOOKUP(RankLN!D45,{0,0.30001,0.70001},{"L","M","W"}),"")