Google Sheets: copying a formula to all cells in a column

Question

When I add formulas to a google sheet they work fine, but when I add a new row the formulas are not copied. to overcome this I found the formula arrayformula. This is good for simple applications such as in cell D4 the following formula is added

=ARRAYFORMULA(if(isblank(B4:B), ,B4:B*C4:C))

However, when the formula is made a little more complicated it breaks down.

=ARRAYFORMULA(if(and(isblank(B4:B),isblank(C4:C)), ,B4:B*C4:C))

This is still a very simple formula that seems to break, whereas the formulas in my sheet are often much more tricky such as

=if(Y5="","",filter('World Clock'!B:B,'World Clock'!A:A=Y5))

Which reads the data of the cell Y5 (which is the name of a timezone which i enter manually) and looks up another sheet named "World clock" which has a list of timezones and formulas for the current time in those timezones and copies that formula to this sheet.

Does anyone know any formula to get this working? I know there is a way to do it via scripts and I'm sure I can figure that out myself, but if I can get the array formula or something similar working that would be much better as I already have a lot of scripts running in this file, it's starting to become slow.

Answer 1

AND() and OR() doesn't work with ARRAYFORMULA() since it performs AND()/OR() method in the whole array.

If you want to do AND/OR per array. Use * = AND() or + = OR() instead.

Example:

AND: =ARRAYFORMULA(if(isblank(B4:B)*isblank(C4:C),,B4:B*C4:C))

OR: =ARRAYFORMULA(if(isblank(B4:B)+isblank(C4:C),,B4:B*C4:C))

---

Output:

---

(Update)

If you want to apply ARRAYFORMULA() in your FILTER() formula, you can use this formula in Trial Sheet A2

=ARRAYFORMULA(if(B2:B="","",Vlookup(B2:B,'World Clock'!A:B,2,false)))

NOTE: You need to remove other formula added from A3 onward since it will block arrayformula() cell writing.

Output: