Google Sheets Recursive Array Formula

0
votes

I'm trying create a list of sequential dates from a set date to the most recent date in another column.

=ARRAYFORMULA(IF(OR(A2:A=MAX(C:C),A2:A=""),"",DATEVALUE(A2:A+1)))

I use MAX() to find the dates most recent date in column C. What I'm trying to get this formula to do is to recursively check the date in the cell above to determine if the max date has been reached. I've made sure A2 already has the set starting date.

The output is in only one cell though and I don't know why.

Thank you for the help.

1
google-sheetsarray-formulas
so this formula sits in A3? – Jeremy Kahan
yes the formula sits in A3 – Kian Nikzad
probably you did this already, but it works nicely if in A3 you put IF(OR(A2=MAX(C:C),A2=""),"",DATEVALUE(A2+1)) and drag that down. That narrows it down to being something about trying to do it in an array formula. – Jeremy Kahan
Did you have to set file spreadsheet setting iterative calculation on? Otherwise, I see an error about a circular reference. – Jeremy Kahan

1 Answers

1
votes

What appears to be troubling your current array formula is that as defined it goes on forever and is self-referential. There may be a way to make it by setting preferences to be iterative and helping it interpret getting to repeated cells with "" as convergence. Here is a way to sidestep those issues. You can bulletproof it more (for example, by encasing it in an IFERROR), but basically you can calculate exactly how many entries you need, and then set your range in the Array formula accordingly. In A3 you place the following:

=arrayformula(DATEVALUE(Row(indirect("A3:A"&(max(C:C)-A2+2)))+A2-2))

which will construct the exact range you want, then calculate each entry with an explicit rather than recursive formula.

EDIT: the above implementation assumes you need at least 2 dates. You can handle that case and other weird ones with the following, less readable formula, =if(max(C:C)>A2,iferror(arrayformula(DATEVALUE(Row(indirect("A3:A"&(max(C:C)-$A$2+2)))+$A$2-2))),"")