Run the function when the first 5 cells have at least one figure

[Rookieeee]

Hello there,
I am currently using this function

=INDEX(('1'!$A2:$A262),MATCH(MAX('1'!B2:B262),'1'!B2:B262,0))

where B2:B262 includes figures and NAs (text).
What I would like to do is to run this function when the first 5 cells (B2:B6) in B2:B262 have at least one figure. Otherwise, return 'NA' when all of the first 5 cells are NAs

Can you guys please help me out?

Thank you so much

Regards

 

[Trebor76]

What I would like to do is to run this function when the first 5 cells (B2:B6) in B2:B262 have at least one figure. Otherwise, return 'NA' when all of the first 5 cells are NAs

There's no need to test as the result would be #NA if any of the cells in B2:B6 are in error anyway

That said you could use this:

=IF(ISERROR(SUM('1'!B2:B6)),NA(),INDEX(('1'!$A2:$A262),MATCH(MAX('1'!B2:B262),'1'!B2:B262,0)))

Regards,

Robert

 

[Rookieeee]

Hello Robert,

The NAs in the selected cells are text, so the sum of b2:b6 is not an error although they are all NAs. Can you please figure it out?

Thank you

Regards,
Alby

 

[Trebor76]

Oh OK. Maybe this then:

=IF(COUNTIF('1'!B2:B6,"NA")=5,"NA",INDEX(('1'!$A2:$A262),MATCH(MAX('1'!B2:B262),'1'!B2:B262,0)))

 

[Rookieeee]

Oh OK. Maybe this then:

=IF(COUNTIF('1'!B2:B6,"NA")=5,"NA",INDEX(('1'!$A2:$A262),MATCH(MAX('1'!B2:B262),'1'!B2:B262,0)))

Thanks a a lot, I tried sum=0, it works.

Thank you so much Robert!!!!!!!!!!

 

[Trebor76]

Thanks for letting us know and you're welcome

 

[Peter_SSs]

What I would like to do is to run this function when the first 5 cells (B2:B6) in B2:B262 have at least one figure.

You could also turn the formula around a little and do this test directly

=IF(COUNT('1'!B2:B6),INDEX(('1'!$A2:$A262),MATCH(MAX('1'!B2:B262),'1'!B2:B262,0)),"NA")