Podcast: Play in new window | Download

Subscribe: Spotify | 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 FREQUENCY(), COLUMN(), MAX(), and the ARRAYFORMULA() functions in Google Sheets.

Here’s the full formula below to calculate win streaks in the Google Sheet:

=ARRAYFORMULA(MAX(FREQUENCY(IF(B2:P2="W",COLUMN(B2:P2)),IF(B2:P2="L",COLUMN(B2:P2)))))

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.

Array-entered formulas

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