Brought to you by:

Hi Reader,

Welcome to the Google Sheets Tips newsletter #338, your Monday morning espresso, in spreadsheet form!

After a last bite from winter last week, it looks like spring is finally here where I live on the east coast of the US. The daffodils are growing, the box turtles are out sunning themselves on logs by the river, and temperatures are forecast in the 70s (in the 20s celcius).

Wherever you are, I hope you're enjoying the changing of the seasons.

➜ News

I.
​Google launched a new Data Science Agent last week that is available through Google Colab. It's an AI tool that generates Python code specifically for data analysis and data science tasks.

​Read more here >>​

➜ Membership

Last week in Sheets Insiders, we had a fantastic turnout of almost 200 people for our live workshop: Unlocking the Secrets of LAMBDA Functions

Normally the replays are for members-only, but since last week's workshop was open to everyone, you're welcome to watch the replay here:

​

Today's tip — how to count winning streaks — was one of the examples we shared in last week's workshop.

Check it out below our sponsor message.

Brought to you by: SheetWhiz The Tool Top Tech Professionals Use For Data Analysis in Google Sheets SheetWhiz is a Chrome extension that brings powerful Excel functionality to Google Sheets. Over 10,000 professionals at companies like Salesforce, DoorDash, Robinhood, and Databricks use SheetWhiz to work faster, stay accurate, and reclaim their productivity. Key Features: Trace Precedents & Dependents: Know your analysis inside and out. Excel Shortcuts: Insert rows, auto-fit column width, paste special and more! Format Cycling: Apply custom formats to numbers with a single shortcut (similar to Macabacus). Export Cell Selections as Images: Paste linked screenshots of your data in Slides. And yes – we work on a Mac! TRY SHEETWHIZ FREE

➜ Sheets Tip #338: Win Streak Formula

Those of you who've read this newsletter for a while will know that I'm a fan of Liverpool Football Club. They play football (or soccer for my US friends) in the English Premier League and are currently enjoying a fantastic season, sitting top of the league. Go Reds!

Their win/draw/loss record for the season so far is:

Looking good!

Today, let's create a formula that counts the longest winning streak, i.e. the maximum number of consecutive "W".

First, let's split that win streak out into separate cells, and transpose to a column view with this formula:

Our data looks like this:

Next, we use a SCAN function — which is a LAMBDA helper function — to add a count for each "W".

Enter this formula in cell B4:

How does this formula work?

We give the SCAN function a start value (0) and then add 1 to it every time there is a "W" in that row. If there is not a "W", it resets to 0 and starts the "W" count over again.

Finally, we use this simple MAX function to find the longest win streak:

which looks like this in our Sheet:

And if you're a formula fiend, feel free to combine them all into a single formula:

For more LAMBDA resources, check out this resource.

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Oh good, it's not just me then​