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
1	ID	Name	Test Score
2	123456	Student A	76%	FALSE
3	654321	Student B	86%	TRUE
4	435261	Student C	96%	TRUE
5	123456	Student A	66%	FALSE
6	654321	Student B	77%	TRUE
7	435261	Student C	46%	TRUE
8	123456	Student A	56%	FALSE
9	654321	Student B	81%	TRUE
10	435261	Student C	88%	TRUE
11	123456	Student A	86%	FALSE
12	654321	Student B	97%	TRUE
13	435261	Student C	79%	TRUE

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula D2 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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
1	ID	Name	Test Score
2	123456	Student A	76%	FALSE
3	654321	Student B	86%	TRUE
4	435261	Student C	96%	TRUE
5	123456	Student A	66%	FALSE
6	654321	Student B	77%	TRUE
7	435261	Student C	46%	TRUE
8	123456	Student A	56%	FALSE
9	654321	Student B	81%	TRUE
10	435261	Student C	88%	TRUE
11	123456	Student A	86%	FALSE
12	654321	Student B	97%	TRUE
13	435261	Student C	79%	TRUE

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula D2 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.