How to count consecutive wins (formula)

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)))) [A formula to return the longest winning streak]( [[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 The Exceljet newsletter is free. We don't share your email with anyone and you can unsubscribe at any time. To get an idea of the topics we cover, see this [summary]( of recent emails. Sign up on our home page [here](. [View this email in a browser]( [Exceljet Logo]( Exceljet P.O. Box 4804 Salt Lake City, UT 84110 Copyright © 2024 Exceljet, All rights reserved. You received this email because you are subscribed to our newsletter. To unsubscribe, click the link below. [Unsubscribe](