ISNA Formula/Query

[Arts]

Hi all

I have a list of values over three rows in which I need to return the non #N/A value. Doing this over two rows isn't a problem but I've become stuck in finding a quick solution to do this over three rows.

Ie over two rows (below) would be to use the ISNA function

Three rows ?

Just to expand on this I am using 2016 (this particular office is soon to upgrade to 365). If I could have a solution for 2016 that would be great but I then attempted this on 365 (work laptop) by using the FILTER function. I thought perhaps this would be the best way to do this for when this particular office upgrades to 365 but this fell flat,

I tried the below which gave me #VALUE

=FILTER(B2:D4,B2:D4<>"N/A")

Just to summarise if I could have a 2016 solution to return the non #N/A value that would be great and if possible as to why my FILTER solution seems to throw an error.


Apologies for using images as when I pasted the data in it seemed to paste it in as a really long table.

many thanks

 

[Arts]

Got it, thanks!

Is it possible to not include #/NA within the FILTER function. So if I had the below table

View attachment 93250

and did =FILTER(E20:F22, F20:F22>55) I would get

View attachment 93251

What would I need to do to get the below. I tried =FILTER(E20:F22,E20:E22<>"#N/A") but this seem to throw up a #VALUE

View attachment 93252

 

[Arts]

not sure if images are showing tried to re post but seem to get an error...

 

[Arts]

Is it possible to not include #/NA within the FILTER function. So if I had the below data

How would I get the below using the FILTER function

I tried =FILTER(E20:F22,E20:E22<>"#N/A") but this seem to throw up a #VALUE

 

[Joe4]

Try:

=FILTER(E20:F22,NOT(ISNA(E20:E22)))

 

[Arts]

Try:

=FILTER(E20:F22,NOT(ISNA(E20:E22)))

That is exactly what I was after!! Thanks Joe!!! This was driving me nuts!!!! Was becoming ISNA obsessed (almost gave up when the images didn't load for some reason) Would never have thought to used the NOT function assumed the "<>" would have done it.

Joe if you ever need directions around London let me know! (only way I can think to repay with knowledge you may not know)

 

[Joe4]

That is exactly what I was after!! Thanks Joe!!! This was driving me nuts!!!! Was becoming ISNA obsessed (almost gave up when the images didn't load for some reason) Would never have thought to used the NOT function assumed the "<>" would have done it.

Joe if you ever need directions around London let me know! (only way I can think to repay with knowledge you may not know)

You are welcome! Glad we were able to help!

The issue is the "#N/A" is not literal text, it is an error code.
So <>"#N/A" is saying not equal to the literal text string "#N/A".
You can use the ISNA function to check to see if it is the error code.

 

[Arts]

Hi Joe

Didn't want to start a new post as what I wanted to ask was actually covered by yourself here but is more based around understanding.

I asked a week or so ago around trying to give back the the non ISNA value over three cells and the formula you provided was great (below)

You should be able to simply nest an IF statement, something like:
=IF(ISNA(B2),IF(ISNA(C2),D2,C2),B2)
]

What I wanted to ask was around the below, when constructing NESTED IF's pretty much every video tutorial I have seen has always seemed to nest the IF in FALSE part of the second IF statement but on your reply you have done this in TRUE of the first IF part. What I wanted to ask is there a general rule of thumb of when to do this or is it case of I guess knowing by experience as such

The issue with the function you posted above is that you are returning C9 before checking to see if it is ISNA. So you would need to order it the way I showed you above.

 

[Joe4]

What I wanted to ask is there a general rule of thumb of when to do this or is it case of I guess knowing by experience as such

Nesting it in the FALSE argument is probably the most common, but it is not because of any general "rule of thumb". It is more dictated by scenario and preference.

In this case, using the ISNA function, it is easy to check to see if a value is equal to "#ISNA. However, it would be more cumbersome to change the condition to try to check for it NOT equal to ISNA.

So let scenario and logic be your guide and dictate how it all flows.

 

[Arts]

Nesting it in the FALSE argument is probably the most common, but it is not because of any general "rule of thumb". It is more dictated by scenario and preference.

In this case, using the ISNA function, it is easy to check to see if a value is equal to "#ISNA. However, it would be more cumbersome to change the condition to try to check for it NOT equal to ISNA.

So let scenario and logic be your guide and dictate how it all flows.

Understood, thanks Joe!

 

[Joe4]

You are welcome.