A collection of Excel formula snippets I've made for seemingly simple but surprisingly difficult to code tasks.
When you have a column merged vertically in sections to group data in the column to the right of it and you want a rolling last-5 average of how many cells of the right column are in each group:
=LET(dates,MERGED_CELL_RANGE_HERE,names,OTHER_RANGE_HERE,ids,SCAN(0,dates,LAMBDA(a,c,a+(c<>""))),v,FILTER(ids,names<>""),u,UNIQUE(v),f,FREQUENCY(v,u),count,ROWS(u),s,IF(count>=5,SEQUENCE(6,,count-5),SEQUENCE(count)),ROUND(AVERAGE(INDEX(f,s)),0))
| Date Added | Name |
|---|---|
| 6/19/2025 | John |
| (merged) | Jane |
| (merged) | Jack |
| (merged) | Zoe |
| 6/20/2025 | Jeff |
| (merged) | Bobby |
| (merged) | Lane |
| 6/21/2025 | John |
| (merged) | Jane |
| (merged) | Jack |
| 6/22/2025 | Caitlyn |
| (merged) | Bobby |
| (merged) | Lane |
| 6/23/2025 | John |
| (merged) | Jane |
| (merged) | Jack |
| 6/24/2025 | Jeff |
| (merged) | Bobby |
| (merged) | Lane |
Result: 3
When you have data like in the previous one and you want to spill an array with dates on the left and number of names on the right:
=LET(dates, A2:A2000, names, B2:B2000, ids, SCAN(0, dates, LAMBDA(a,c, a + (c<>""))), filteredIds, FILTER(ids, names <> ""), uniqueIds, UNIQUE(filteredIds), firstDatePerGroup, MAP(uniqueIds, LAMBDA(id, INDEX(dates, MATCH(id, ids, 0)))), countsPerGroupAll, FREQUENCY(filteredIds, uniqueIds), countsPerGroup, TAKE(countsPerGroupAll, ROWS(uniqueIds)), HSTACK(firstDatePerGroup, countsPerGroup))
| 6/19/2025 | 4 |
|---|---|
| 6/20/2025 | 3 |
| 6/21/2025 | 3 |
| 6/22/2025 | 3 |
| 6/23/2025 | 3 |
| 6/24/2025 | 3 |