Counting consecutive test results above 75% per student

[Phil1981]

I have a large data dump of 1200+ students and all their test scores. What I want to do is output a results (true/false) for each agent that had at least 2 consecutive test scores above 75%

Example
ID | Name | Test Score
123456 | Student A | 76%
654321 | Student B | 86%
435261 | Student C | 96%
123456 | Student A | 66%
654321 | Student B | 77%
435261 | Student C | 46%
123456 | Student A | 56%
654321 | Student B | 81%
435261 | Student C | 88%
123456 | Student A | 86%
654321 | Student B | 97%
435261 | Student C | 79%

In this case Student A would result in False (no 2 consecutive scores above 75%), Student B would result in True (all 4 tests above 75%) and finally Student C would result in True (last 2 test were above 75%)

I thought it would simply to formulate but I can't seem to get the result I was expecting using any formulas I am familiar with.

 

[Eric W]

It's a pretty tricky proposition. Try:

	A	B	C	D
ID	Name	Test Score
Student A
Student B
Student C
Student A
Student B
Student C
Student A
Student B
Student C
Student A
Student B
Student C

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]76%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]86%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]96%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]66%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]77%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]46%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]56%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]81%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]88%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]86%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]97%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]79%[/TD]
[TD="align: right"]TRUE[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=IF(MAX(FREQUENCY(IF(($A$2:$A$13=A2)*($C$2:$C$13>=75%),ROW($A$2:$A$13)),IF(($A$2:$A$13=A2)*($C$2:$C$13<75%),ROW($A$2:$A$13))))>=2,TRUE)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Phil1981]

Sorry I forgot to mention the additional criteria I need which complicates this request even more, Each student has 12 weeks to accomplish 2 scores above 75% so I need a date range criteria as well.

Example is Student A started into this new pilot on 9/1/2017 and must have accomplished 2 consecutive scores above 75% by 11/24/2017 to be True.

 

[Phil1981]

It's a pretty tricky proposition. Try:

	A	B	C	D
ID	Name	Test Score
Student A
Student B
Student C
Student A
Student B
Student C
Student A
Student B
Student C
Student A
Student B
Student C

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]76%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]86%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]96%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]66%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]77%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]46%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]56%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]81%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]88%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]86%[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"]97%[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]435261[/TD]

[TD="align: right"]79%[/TD]
[TD="align: right"]TRUE[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=IF(MAX(FREQUENCY(IF(($A$2:$A$13=A2)*($C$2:$C$13>=75%),ROW($A$2:$A$13)),IF(($A$2:$A$13=A2)*($C$2:$C$13<75%),ROW($A$2:$A$13))))>=2,TRUE)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Ok this formula should work even with my additional criteria because I can just add a helper column for the end date as I already have their start date. I will try this when I am back in the office tomorrow. Thanks so much, I will let you know if it works.