Came across something interesting that I had never used before, thought it might be interesting to some here.

My brother asked for help with a data set. There are 50 columns representing the last 50 years. For each row, there may be data in each row for some, all, or none of the 50 years. What's more, the data is not necessarily continuous (ie it can skip years). The ask was to do conditional formatting on the data set to color each row based on the number of years that have data - easy enough. But a secondary goal was to format based on the largest number of years with continuous data in each row. I ended up coming up with various relatively messy solutions, but my brother-in-law found this, which worked beautifully:

https://exceljet.net/formula/count-consecutive-monthly-orders`{=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng))))}`

Gorgeous bit of code, using the FREQUENCY function, which I had never used before.