I have a dataset in which the rows are a student's performance on a test, and the columns are the individual test questions. I would like to create a formula that sums the columns for a student (the test performance; 0 = incorrect, 1 = correct) until there are four 0s in a row. For instance, if a student scored:
Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 1 1 1 1 1 1 0 1 1 0 0 0 1 0 0 0 0 1 1 0
The formula should return 9, because it sums until Q17 where the condition is met (four straight incorrect responses).
Presume performance on the first question for the first student is cell
B2 (first row is headers, first column is student name).
Any help would be much appreciated, because this exceed my excel knowledge.