Dear Analyst Episode 22: Calculate win streaks for a pool of players in Google Sheets
Podcast: Play in new window | Download
Subscribe: Google Podcasts | Spotify | Stitcher | TuneIn | RSS
If you are by your computer, you may want to open this Google Sheet to understand the example discussed in this episode. I walk through a rather long formula involving the
MAX(), and the
ARRAYFORMULA() functions in Google Sheets.
Here’s the full formula below to calculate win streaks in the Google Sheet:
You can see an embed of the Google Sheet below as well:
Calculating win streaks with bye weeks
What made this problem interesting is that if there are Xs in the weeks, those are considered bye weeks for the players and shouldn’t count against the player. In the Google Sheet, you’ll notice that John has a X for Week 3, but has Ws for Weeks 1, 2, 4, and 5. Just because he had a bye week for Week 3 means he still has a 4-game winning streak for those 5 weeks.
This question originally appeared on the Mr. Excel forum here. The answer involves (in my opinion) a non-traditional use of the
FREQUENCY() function to find the number of Ws for a given player that fall between different weeks where the player took an L.
For those not familiar with the
FREQUENCY() function, it’s one of the special functions in Excel that must be array-entered into Excel. Traditionally, this means while you’re editing the formula, you hit
CTRL+SHIFT+ENTER to array-enter the formula. In newer versions of Excel, you don’t have do the CSE combination as Excel just knows to apply the formula to an array of cells.
CSE doesn’t exist in Google Sheets, so in order to array-enter formulas, you just use the
ARRAYFORMULA() function to tell Google Sheets this formula should be applied to a range of cells.
Other Podcasts & Blog Posts
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
- Freakonomics: Episode #391: America’s Math Curriculum Doesn’t Add Up
Doesn’t work properly as your formula is stated. I have no idea how to fix it.
Did you press
CTRL+SHIFT+ENTERto array-enter the formula?