CODEWITHSUNDEEP
google-sheetsarray-formulas
Argyll
Argyll

Reputation: 9875

How to make Google Sheet arrayformula work with date functions

I have a column of dates with the format "yyyy-mm-dd". And I want to do summary statistics by month -- using native formulas.

When I put arrayformula around month(...)>month(...), I get the error

Result was not expanded automatically, please insert more rows (1).

Why is that? And how do I get around that?

Example sheet. Formula in B4.

=arrayformula(month(B4:B)>month(B3:B))

Note: I don't know the number of rows ahead of time -- it is expected to update from time to time.

Upvotes: 0

Views: 873

Answers (2)

Argyll
Argyll

Reputation: 9875

=arrayformula(if(isblank(B4:B),,month(B5:B)>month(B4:B)))

works.

It seems it is always better to include an isblank() wrap to avoid indefinite expansion of the sheet.

Upvotes: 0

MattKing
MattKing

Reputation: 7773

Firstly, erase your formula, it is trying to automatically expand the sheet and the sheet already has over 50,000 rows.

Next delete 48,000 rows or so.

Next put this formula in cell B4:

=arrayformula(month(B4:B)>month(arrayconstrain(B3:B,rows(B4:B),1)))

That will prevent the formula from auto-expanding the sheet.

That should work, though I think you're going to realize you don't like what happens when the year changes. I believe this to be an xyProblem.

Upvotes: 1

Related Questions