Search code examples
if-statementgoogle-sheetssumcell

how to use parenthesis or accomplish complex cell rules upon stated logic

Setup:

enter image description here

Logic:

  • If a person worked 6 days he needs to rest 2 days otherwise cell O3=1
  • Cell O3 is empty if a rule is not violated
  • days are up to 31
  • those 6 days needs to be in a row followed by 2 days in a row
  • point is to check all 26 possible "chunks" of cells

So far I got this:

=IF( OR( IF( AND( SUM(B3:G3)=6; SUM(H3:I3)=0); SUM(C3:H3)=6; SUM(I3:J3)=0) ); 1; ); 0; 1;)

Thoughts?

Solution

  • This answer requires a slight change to you data, but seems to work. It requires replacing the blank cells (non-working days) with zero. The join creates a single string ('1111100111111011111101111110100'). Then the string is searched for occurrences of '11111101'. It counts those occurrences. If count is zero, blank else 'Violation'.

    =if((LEN(join("",B3:AF3))-LEN(SUBSTITUTE(join("",B3:AF3),"11111101",)))/LEN("11111101")=0,"","Violation")

    Copy the formula down.

    @Michael has a good point. I added an OR to the above formula with handles both situations. The one above and 7 or more 1s in a row:

    =if(or((LEN(join("",B3:AF3))-LEN(SUBSTITUTE(join("",B3:AF3),"1111111",)))/LEN("1111111")>=1,LEN(join("",B3:AF3))-LEN(SUBSTITUTE(join("",B3:AF3),"11111101",)))/LEN("11111101")=0,"","Violation")