Matrix Equation Match(Frequency( not stopping after specified number

[Luke_Tk]

Hey all I've got an equation that is working but has an issue.

Objective: to find the amount of time it takes to switch from one activity to the next. In order to say the switch is complete 5 activities must be complete in a certain amount of time.

Attempted Solution: I used a Match(Frequency( matrix formula to find the location where the activity normalizes. I've posted it below:

{=MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0)}

What Info is referenced: Column I is a helper column reporting if the time for the activity is within time parameters, and posts a 1 if it is. Column B is the list of activities. and cell J6 is the activity the user wants to analyze.

Issue with Formula: The formula works fine until there are more than 5 activities within the timeframe in a row that are correct, in which case the formula keeps counting until it find a negative, then reports the last successful one. I need the formula to stop at 5 successful matches.

If it helps anyone with a similar problem the entire formula is posted below though there aren't any problems with the rest other than its length.


=INDEX(E1:E20000,MAX(IF(OFFSET(B2,0,0,MAX(IF(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0) - 1)=J6,ROW(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0) - 1)))) - 1)=J6,ROW(OFFSET(B2,0,0,MAX(IF(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0) - 1)=J6,ROW(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0) - 1)))) - 1)))))