Google sheets IF stops working correctly when wrapped in ARRAYFORMULA-CodePudding

I want this formula to calculate a date based on input from two other dates. I first wrote it for a single cell and it gives the expected results but when I try to use ARRAYFORMULA it returns the wrong results.

I first use two if statements specifycing what should happen if either one of the inputs is missing. Then the final if statement calculates the date if both are present based on two conditions. This seems to work perfectly if I write the formula for one cell and drag it down.

=IF( (LEN(G19)=0);(U19 456);(IF((LEN(U19)=0) ;(G19);(IF((AND((G19<(U19 456));(G19>(U19 273)) ));(G19);(U19 456))))))

However, when I want to use arrayformula to apply it to the entire column, it always returns the value_if_false if neither cell is empty, regardless of whether the conditions in the if statement are actually met or not. I am specifically talking about the last part of the formula that calculates the date if both input values are present, it always returns the result of U19:U 456 even when the result should be G19:G. Here is how I tried to write the ARRAYFORMULA:

={"Date deadline";ARRAYFORMULA(IF((LEN(G19:G400)=0);(U19:U400 456);(IF((LEN(U19:U400)=0); (G19:G400);(IF((AND((G19:G400<(U19:U400 456));(G19:G400>(U19:U400 273)) ));(G19:G400);(U19:U400 456)))))))}

I am a complete beginner who only learned to write formulas two weeks ago, so any help or tips would be greatly appreciated!

CodePudding user response：

AND and OR are not compatible with ARRAYFORMULA

Replace them by * or

Try

={"Date deadline";ARRAYFORMULA(
 IF((LEN(G19:G400)=0),(U19:U400 456),
  (IF((LEN(U19:U400)=0), (G19:G400),
   (IF((((G19:G400<(U19:U400 456))*(G19:G400>(U19:U400 273)) )),(G19:G400),
    (U19:U400 456)))
  ))
 )
)}

CodePudding user response：

Keep in mind you cannot use AND, OR operators in an arrayformula, so you must find an alternative method such as multiplying the values together and checking them for 0 or 1 (true*true=1)

I am gathering based on your formula's and work that you want to have the following:

If G19 is blank show U19 456
If U19 is blank show G19
If G19 is less than U19 456 but greater than U19 273 show G19
Otherwise show U19 456

I'm not too sure what you want to happen when both columns G and U are empty. Based on your current formula you are returning an empty cell 456... but with this formula it returns an empty cell rather than Column U 456

Formula

={"Date deadline";ARRAYFORMULA(TO_DATE(ARRAYFORMULA(IFS((($G19:$G400="")*($U19:$U400=""))>0,"",$G19:$G400="",$U19:$U400 456,$U19:$U400="",$G19:$G400,(($G19:$G400<$U19:$U400 456)*($G19:$G400>$U19:$U400 273))>0,$G19:$G400,TRUE,$U19:$U400 456))))}