Array formula in googlesheets being errored out by new row-CodePudding

I am using the array formula below to apply the same formula to new rows when they are added. However, when new rows are added, it messes up the formula (I think it is autocorrecting, or something). I went to Tools and turned off all autocomplete functions. Does anyone know what could be happening?

The formula I am using:

=ARRAYFORMULA(IF(LEN(C2:C),IF(C2:C<TODAY(),1,0),))

CodePudding user response：

try to freeze it:

=ARRAYFORMULA(IF(LEN(INDIRECT("C2:C")),IF(INDIRECT("C2:C")<TODAY(),1,0),))

or if this is a form sheet use this formula in row 1:

={""; ARRAYFORMULA(IF(LEN(C2:C),IF(C2:C<TODAY(),1,0),))}

CodePudding user response：

You're missing a "false" case in the outer IF statement.

=ARRAYFORMULA(IF(LEN(C2:C),IF(C2:C<TODAY(),1,0),**missing**))

Also I don't think you can use a column for the len() function. I'm not sure what the context of this formula is but I suggest trying to remove the ARRAYFORMULA() like so:

IF(LEN(C1),IF(C1<TODAY(),1,0),**some false case**)