Hi - Dave here.
Happy Friday!
How can you count consecutive wins in Excel?
This is a traditionally challenging problem in Excel because it's not obvious how to solve it. Excel has many functions designed to count things, but none are dedicated to counting
consecutive things.
Until now, the simplest way to solve this problem is to create a helper column with a running count of wins, and then use the MAX function to find the maximum count. However, the SCAN function makes it easy to use one formula, as seen in the worksheet below. The formula in cell E5 is:
=MAX(SCAN(0,C5:C22,LAMBDA(a,v,IF(v="w",a+1,0))))
[
Download the workbook and read the full explanation]
The SCAN formula may look scary, but it is actually pretty simple. Starting at zero, SCAN runs through the list of results. When it encounters a "w", it adds 1 to a running count. When it encounters any other value, it resets the running count to zero. SCAN returns the full set of running counts to MAX, which returns the maximum number. Download the worksheet and have a look yourself.
Although we are specifically counting the longest winning streak in this example, the same approach can be used to count many other things, including:
- Consecutive days of exercise.
- Consecutive days without symptoms.
- Consecutive days practicing a language or skill.
- Consecutive days not drinking, or not smoking.
- Consecutive days without an accident (at a factory for example).
The SCAN function requires Excel 365. However, the article above includes two approaches that will work in any version of Excel.
More leap years
Last week, I shared a
formula to test for leap years. The problem is surprisingly tricky because of quirks in our modern calendar system, and quirks in Excel. On that same topic, I wanted to share a
another formula you can use to
generate a list of leap years. This is a nice example of how the SEQUENCE function can be used to solve interesting problems.
This formula requires Excel 2021 or Excel 365.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
