I wrote a VBA user-defined function for this task, but I can’t help but think there’s a worksheet formula that can do the job. In case you were looking for something to do this weekend, here’s something to occupy your time.
You get a two column range, the starting group, the ending group, the number of days, and the threshold. The formula should return the number of groups between 2 (E2) and 4 (E3) inclusive that have at least 2 (E4) consecutive amounts greater than or equal to 400 (E5).
The answer is 2. Groups 1 and 5 are outside the range, so they don’t count. Group 2 only has 1 consecutive amount over the threshold. Groups 3 and 4 both have 2 consecutive amounts over the threshold. I should be able to change the inputs in column E to change the answer.