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

 

[Joe4]

So, what exactly is the order of precedence?

You should be able to simply nest an IF statement, something like:

=IF(ISNA(B2),IF(ISNA(C2),D2,C2),B2)

 

[Arts]

So, what exactly is the order of precedence?

You should be able to simply nest an IF statement, something like:

=IF(ISNA(B2),IF(ISNA(C2),D2,C2),B2)

Thanks Joe

Order of precedence was a 2016 solution which you have provided, the above works! I thought there may have been a "simpler/efficient" way as such I did try a nested IF but seemed to have got it completely wrong (below) will take a look and see where I went wrong.

In my head I thought there may have been a way to select the below as an array and return the value...

If I was using 365 version is there a reason why my FILTER solution wouldn't work ? I may completely be on the wrong path with that idea but got curious if that was a viable way to go

 

[Fluff]

Another option would be

=AGGREGATE(15,6,B2:D2/(ISNUMBER(B2:D2)),1)

 

[Joe4]

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.

As far as I know, the FILTER function is used to return whole rows of data. So it is the row that is the variable part (whether to return it or not), not the column part.
The number of columns to be returned is static, and cannot be shifted, per row, as far as I know.
It works very similar to the old Excel Filter functionality, if you are familiar with that, which really is just used to hide rows not meeting designated criteria.

 

[Arts]

Another option would be

=AGGREGATE(15,6,B2:D2/(ISNUMBER(B2:D2)),1)

Thanks Fluff that works like a charm. I guess it's all relative but that also seems quite complex to me as in from =IF(ISNA(B2),C2,B2) over two rows to the AGGRGEATE method over 3 rows.

Always amazes me how increasing the range in this case by 1 leads to an avenue that I doubt I would ever have seen.

Would my FILTER avenue be a no go ?

 

[Arts]

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.

As far as I know, the FILTER function is used to return whole rows of data. So it is the row that is the variable part (whether to return it or not), not the column part.
The number of columns to be returned is static, and cannot be shifted, per row, as far as I know.
It works very similar to the old Excel Filter functionality, if you are familiar with that, which really is just used to hide rows not meeting designated criteria.

I see, thanks for the explanation Joe on the Formula and the Filter function. I was under the impression with the Filter function I could "Filter" results but after trying a few examples you can Filter a specific range ie if I had names and scores in the INCLUDE section of the FILTER function I would only select the scores data to give me anything over 60 for example and not the whole data set.

 

[Joe4]

I was under the impression with the Filter function I could "Filter" results

You can, and it does, but you need to understand that Filter works horizontally (hide rows) and not vertically (hide columns).

 

[Arts]

Got it, thanks both once again!

 

[Joe4]

You are welcome.
Glad we were able to help!