(Sorry but I cannot get a table to work...)
I'm trying to create a counter of the maximum daily "win streak" of a specific activity. However, in any given day it's perfectly reasonable to have not attempted the activity in question at all. Weekends in particular are the usual culprit. So I need to be able to count the largest "winning streak" that can have days which are skipped.
Data example: |Date|Win| |----------|-------| |Mon, Sep 12|FALSE| |Tue, Sep 13|TRUE| |Wed, Sep 14|FALSE| |Thu, Sep 15|TRUE| |Fri, Sep 16|TRUE| |Sat, Sep 17|-| |Sun, Sep 18|-| |Mon, Sep 19|TRUE|
| Date | Win |
|---|---|
| Mon, Sep 12 | FALSE |
| Tue, Sep 13 | TRUE |
| Wed, Sep 14 | FALSE |
| Thu, Sep 15 | TRUE |
| Fri, Sep 16 | TRUE |
| Sat, Sep 17 | - |
| Sun, Sep 18 | - |
| Mon, Sep 19 | TRUE |
So in this example, the largest win streak is 3. Saturday and Sunday are ignored as nothing happened those days.
I currently am using a formula at this page and it works but does not ignore days without activity:
=sortn(frequency(if((Q2:Q>0)*len(Q2:Q),row(Q2:Q)),if((Q2:Q>0)*len(Q2:Q),,row(Q2:Q))),1,0,1,0)
How can I modify this formula to ignore "null" days, or use a different single-formula (no helper columns etc.) approach?
