Statistical
Longest winning or daily habit streak formula (current and best streak)
Track consecutive wins, incident-free days, or SLA hits with a resetting streak counter and summarize Best and Current Streak.
Resetting running counter + MAX summary
Counting total "W" rows gives total wins, not consecutive wins. A running streak column increments the previous row's streak by 1 whenever the current row equals "W" and resets immediately to 0 on any loss: IF(B3="W", N(C2) + 1, 0).
Using N(C1) on the first data row safely converts the text header "Streak" to 0 so the exact same formula works from row 2 to the bottom. MAX(C2:C7) then reports the longest streak within the selected range, and TAKE(C2:C7, -1) reports the active current streak.
| Day (A) | Outcome (B) | Running Streak (C) |
|---|---|---|
| 1 | W | 1 |
| 2 | W | 2 |
| 3 | W | 3 (Peak) |
| 4 | L | 0 (Reset) |
| 5 | W | 1 |
| 6 | W | 2 (Current) |
Longest streak and current active streak formulas
Across W, W, W, L, W, W, longest streak is 3 and current streak is 2.
=MAX(C2:C7)Active current streak at bottom of log
INDEX(C2:C7, ROWS(C2:C7)) (or TAKE(C2:C7, -1)) pulls the latest active streak.
=INDEX(C2:C7, ROWS(C2:C7))Inputs and interpretation
This counts consecutive recorded rows. For daily habits, require one chronological record per calendar day and handle missing dates explicitly; otherwise nonconsecutive days can appear as a streak. Any non-W value, including a blank, resets the counter. Exclude future/unplayed rows when reading the current streak, and verify predecessor references after structural edits.
Try the example
Use the original values or change them to test your understanding.