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  Reply With Quote

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")))  Reply With Quote

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,  Reply With Quote

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?  Reply With Quote

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"}),"")  Reply With Quote

User Tag List

Tags for this Thread

excel, function, ignore, repeat, run  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•