I am trying to come up with a formula that puts an “OK” value in my Notes tab if the row has 2 or more consecutive 1 values. My rows have many more columns that just up to “N”, this is just a screenshot I am using to try and make my question clearer.
I am admittedly a very novice excel user. Can anyone help me out here?
1
Thought I’d try a different approach.
TEXTJOIN("|",FALSE,$J2:$N2) will stick all the cells together separated by a pipe character.
If two cells contain a 1 you’ll get a 1|1 returned somewhere within the resulting text string, so you just need to find that.
=IF(ISNUMBER(FIND("1|1",TEXTJOIN("|",FALSE,$J2:$N2))),"OK","")
The formula used
=LET(a,G4:M4,
b,OFFSET(a,0,1),
IF(SUM(IF((a=1)*(b=1),1,0))>0,"OK",""))
adapted to your actual table
=LET(a,J8:N8,
b,OFFSET(a,0,1),
IF(SUM(IF((a=1)*(b=1),1,0))>0,"OK",""))
Another option using SUMPRODUCT:
=IF(SUMPRODUCT(--(J2:M2=1),--(K2:N2=1)),"OK","")
1
I would use SCAN for anything that has the word “consecutive” in it:
Formula in F4 (can be filled down for the corresponding row):
=IF(OR(SCAN(0,G4:M4,LAMBDA(aggregated,current,(aggregated+current)*(current=1)))>=2),"OK","")