I have the following data (created through a ARRAYFORMULA formula):
| A | B | C | D | |
|---|---|---|---|---|
| 1 | TRUE | TRUE | FALSE | TRUE |
| 2 | TRUE | TRUE | TRUE | TRUE |
| 3 | TRUE | FALSE | TRUE | TRUE |
If in a row all values are TRUE, the output for that row should be TRUE. If even 1 is FALSE, it should be FALSE instead. So a formula on the above table should output this:
| E | |
|---|---|
| 1 | FALSE |
| 2 | TRUE |
| 3 | FALSE |
Restrictions
- The 1st table's size is not fixed. There could be more columns or rows. Therefore, no manual row by row check.
- It should be 1 function that outputs to multiple rows (like the 2nd table), therefore it should be through the
ARRAYFORMULAformula (not dragging a cell down, as this works badly when adding rows/columns at a later stage).
What I tried
AND
AND, but that gives only a single output:
=AND(A1:D3)
# FALSE
Also, AND doesn't work with ARRAYFORMULA
*
A * function as AND in ARRAYFORMULA:
=ARRAYFORMULA(A1:A3*B1:B3*C1:C3)
Problems are:
- This is a manual process, which doesn't scale.
- If a column is added later, it will become e.g.
A1:A3*C1:C3*D1:D3instead ofA1:A3*B1:B3*C1:C3*D1:D3
A1:C3*
Unfortunately, this logic is not accepted:
=ARRAYFORMULA(A1:C3*)
QUERY
Maybe a QUERY can provide this logic? But I'm not able to come up with how that query should look like.
Question
How do I do a row-wise check if all values are TRUE with a single ARRAYFORMULA function (taking into account the restrictions mentioned)?
