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

Rookieeee

New Member
Joined
Aug 26, 2019
Messages
22
Hello there,
I am currently using this function
Code:
=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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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 :confused:

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
 
Last edited:
Upvote 0
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
 
Upvote 0
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)))
 
Last edited:
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top