Trying to flag rows: =ARRAYFORMULA(IF(C2:C = "", "", IF(D2:D > E2, "over", "ok"))) in F2. I get a result in F2 and absolutely nothing in F3 downwards, not even blanks. If I drag the plain IF down it works perfectly on every row, so the logic is right. What makes ARRAYFORMULA give up after one row?
Handy debugging habit: pull each piece out into its own cell as a standalone array first. =ARRAYFORMULA(D2:D > E2) on its own shows you immediately that it produces one value rather than a column. Testing the pieces separately finds these in a minute instead of an afternoon.
Minor style point that saves pain later: bound your ranges. C2:C10000 rather than C2:C on a sheet that will grow. Open-ended ranges inside an ARRAYFORMULA make Sheets consider the entire column, and on a big file you will feel it.
Also worth knowing that some functions simply refuse to be arrayed no matter what you do. SUMIF and COUNTIF behave, several lookup wrappers do not. If the shapes all look right and it still collapses, that is usually why.
The reliable workaround for the stubborn ones is BYROW or MAP if you have them. Slower on big ranges but it does what you meant.
@shadowing_sena · 6mo ago · 3 replies
Look at your second condition:
D2:D > E2. You are comparing a whole column against a single cell. ARRAYFORMULA can only expand to the size of the ranges inside it, and mixing a range with a bare relative single cell in a comparison is one of the ways it silently collapses.If E2 is genuinely one threshold value, lock it and it will broadcast to every row:
D2:D > $E$2. If E is meant to be a column of per-row thresholds, it should beD2:D > E2:E. Right now you have written neither.The nothing you are seeing below row 2 is the collapse, not the empty-string branch.
Reply
Report
@csv_apologist · 6mo ago
For anyone finding this later: the general rule is that every range inside an ARRAYFORMULA should either be the same height or be an absolute reference. A bare relative single cell in there means trouble.
Reply
Report
@till_and_tally · 6mo ago
It was meant to be a per-row threshold, so
E2:Ewas the fix. Works down the whole column now. I would never have spotted that on my own.Reply
Report