0

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)))))

enter image description here

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))))))
Improve this question
4
  • 2
    I haven't evaluated your formula, it seems a simpler formula would be: =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.
    – gns100
    Apr 2 at 16:54
  • 1
    Another approach... if the daily results are always presented in tranches of 10 (in other words, you know that Team 02's Day n results are always going to be 10 rows below their Day n-1 results), you can do something like =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 in T so it's possible (even likely) there's something I'm missing about your method that wasn't explained.
    – Alex M
    Apr 2 at 19:38
  • @gns100: But I don't want find the sum per Team of the 1st places, I need the maximum number (per team, per season) of the consecutive days they were leaders.
    – Pesetas74
    Apr 3 at 10:15
  • @AlexM: I did something like that to find the intervals (columns R and S), so I already have them but I need to count the frequency of consecutive first places for each team and each season (also different seasons have sometimes different number of teams and more than 1 group). I'll try to explain better in my post.
    – Pesetas74
    Apr 3 at 10:20
0

I think I've managed to find a way to obtain the maximum number of consecutive days for each Team & Season, although I'm not sure how it works. I've followed the advice in this post on Stack Overflow and added a moltiplication in the array formula

enter image description here

=MAX(FREQUENCY(IF(INDIRECT(G2)=1*(INDIRECT(F2)=C2);ROW(INDIRECT(G2)));IF(INDIRECT(G2)>1+(INDIRECT(F2)<>C2);ROW(INDIRECT(G2)))))

However, when I've applied the formula on the whole table (all Seasons, all Teams), I had to change the ">1" in the above formula into "<1", otherwise the outcome would have been completely "wrong" (not as expected):

=MAX(FREQUENCY(IF(INDIRECT(S2)=1*(INDIRECT(R2)=C2);ROW(INDIRECT(S2)));IF(INDIRECT(S2)<1+(INDIRECT(R2)<>C2);ROW(INDIRECT(S2)))))

Although it looks like it's working, I can't figure out why ...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.