Nested If formula?

One of my colleagues needs to flag each record that has 3 or more consecutive days with a 1 in a column.  Attached simple spreadsheet demonstrates what he's trying to get.
Tricky.xlsx
kerikeriAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
FlysterConnect With a Mentor Commented:
In the first 2 rows, C2 and C3, use this formula:

=IF(SUM(C2:C4)>2,"OK","")

In C4, use this:

=IF(OR(SUM(B2:B4)>2,SUM(B4:B6)>2,SUM(B3:B5)>2),"OK","")

Copy this formula the rest of the way down.

Flyster
Tricky.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

are the values always 1 or can there be other numbers? This one, starting in C2 and copied down will cater for any value greater than 0:

=IF(OR(AND(D1="OK",B2>0),AND(B2>0,B3>0,B4>0)),"OK","")

cheers, teylyn
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Taking teylyn's formula a step further, this formula will find any non-zero number repeated consecutively three or more.

=IF(OR(AND(D1="OK",B2>0),AND(B2>0,B3=B2,B4=B2)),"OK","")
0
 
kerikeriAuthor Commented:
Hey thanks, each of your suggestions was helpful in the final solution.  My colleague is impressed!  Thanks for your quick response, much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.