# Dear Analyst Episode 25: Structuring data challenge (denormalize data) with Get and Transform

This episode is based on a video and Tweet posted by Mr. Excel (Bill Jelen). Bill discusses an Excel challenge someone emailed him about regarding how to “transform” a badly structured table of data into a structure that makes it easy to do PivotTables and other downstream analysis. Interestingly, I received a ticket from a […]

# Dear Analyst Episode 22: Calculate win streaks for a pool of players in Google Sheets

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))))) […]

# Count the number of blank values in a range in Excel

If you want to count the number of cells with blank values in them, you may think it’s as simple as using the COUNTBLANK() formula. Usually you do this if you want to know how much data is missing in your data set. Say you have a list of 10 students, and if a student […]