3-4-21: I've edited the initial post trying to make it clearer, sorry for the misleading information.
I have a spreadsheet with the results of a fantasy football league and I'm trying to calculate some statistics. I'm currently struggling with a formula to calculate the maximum number of consecutive days that each team was leader for each season.
Seasons span from 2015-16 to 2019-20 (for the time being) and the number of teams participating is not fixed (sometimes 8, others 10), as the number of Groups (sometimes 1, others 2). In column "C" I have the name of the Team (the "Team interval" for each season is calculated in column "F") and in column "E" I've calculated the ranking for each Day of each season (the interval for each Season used for ranking is calculated in column "G").
Maybe with the following simplified example I'll explain myself better. Team 2 has been the leader for 11 consecutive days (Days 1 to 11) and then for another 13 consecutive days (Days 13 to 25). Team 5 has been the leader for 11 consecutive days (Days 26 to 36). In column "I" the manually typed result that I'd expect. In column "J" a partially working formula using the variable interval with INDIRECT, which however doesn't take into account the team and counts 24 consecutive days (Day 13 to 36) instead of 13 for Team 2 and 11 for Team 5 separately.
=MAX(FREQUENCY(IF(INDIRECT(G2)=1;ROW(INDIRECT(G2)));IF(INDIRECT(G2)>1;ROW(INDIRECT(G2)))))
It looks like only the last step is missing and I've tried modifying the formula by adding a condition at the bottom on the Team interval column ("F"), but it doesn't work
=MAX(FREQUENCY(IF(INDIRECT(G2)=1;ROW(INDIRECT(G2)));IF(INDIRECT(G2)>1;ROW(INDIRECT(G2));IF(INDIRECT(F2)=C2;ROW(INDIRECT(F2))))))
=sumifs()
. I'm sure you can even adjust the formula so that the result would show up to "Day" so row 3 would show 1 and then in row 13 it would show 2.=IF(Q12=1,Q12+Q2,0)
. You would have to initialize the first 10 rows manually but this formula would paste down from the start of Day 2 results. I also have not evaluated your formula inT
so it's possible (even likely) there's something I'm missing about your method that wasn't explained.