Skip to content
Sheet ErrorSheet ErrorSheet Error

ARRAYFORMULA with IFERROR masks #DIV/0! and #N/A as blank or 0

Updated

ARRAYFORMULA spills one formula down a column. IFERROR around that formula spills too, and it replaces every error with whatever fallback you typed. A #DIV/0! becomes 0 or a blank. A #N/A from VLOOKUP becomes a blank bay. The column has no red triangles. The zeros look like measured rates.

This page is that mask. It is not ARRAYFORMULA AND/OR (those two functions collapse to one boolean). It is not empty-row zeros (a blank cell coerced to 0 in arithmetic). IFERROR versus IFNA as a choice on one cell is IFERROR vs IFNA.

Recreate it on a blank sheet

Section titled “Recreate it on a blank sheet”
  1. Open a blank spreadsheet. Keep the Google Sheets UI in English. In A1 type units, B1 hours. A2 10, B2 2. A3 10, B3 0. A4 5, B4 5. Row 3 divides by zero on purpose.

Unmasked spill. In D1 type raw. In D2 enter =ARRAYFORMULA(A2:A4/B2:B4). D2 shows 5. D3 shows #DIV/0!. D4 shows 1. The tooltip on D3 is the divide-by-zero error. That triangle is the diagnosis. Leave D2:D4 as they are.

Mask to zero. In F1 type mask_0. In F2 enter =ARRAYFORMULA(IFERROR(A2:A4/B2:B4,0)). F2 shows 5. F3 shows 0. F4 shows 1. No triangle on F3. A rate of zero hours-per-unit is a real number in a dashboard. Here it means “B3 was zero and the error was deleted.”

F3 selected. Formula bar shows =ARRAYFORMULA(IFERROR(A2:A4/B2:B4,0)). F3 is 0 with no error triangle. D3 is #DIV/0!. B3 is 0. A3 is 10.

Mask to blank. In H1 type mask_blank. In H2 enter =ARRAYFORMULA(IFERROR(A2:A4/B2:B4,"")). H3 looks empty. =H3="" is TRUE. =ISBLANK(H3) is FALSE, because the spill wrote an empty string. A chart skips the blank and the zero differently. Neither one is #DIV/0!.

Lookup mask. In J1 type sku, K1 bay. J2 101, K2 A. J3 102, K3 B. In M1 type find. M2 101. M3 999. M4 102. In O1 type lookup_mask. In O2 enter =ARRAYFORMULA(IFERROR(VLOOKUP(M2:M4,J2:K3,2,FALSE),"")). O2 is A. O3 looks empty. O4 is B. The unmasked formula =VLOOKUP(M3,J2:K3,2,FALSE) in Q3 is #N/A, tooltip Did not find value ‘999’ in VLOOKUP evaluation. O3 hid that miss. Leave Q3 broken. Leave O3 blank.

=ARRAYFORMULA(IF(ISERROR(A2:A4/B2:B4),"",A2:A4/B2:B4)) is the same mask as H2. ISERROR is true for #DIV/0!, #N/A, #VALUE!, and #REF!. One fallback eats all of them.

What is actually wrong

Section titled “What is actually wrong”
  • IFERROR(value, fallback) returns fallback when value is any error. Inside ARRAYFORMULA, that decision runs on every row. F3’s 0 is the fallback you typed, not 10/0.
  • A blank fallback is still a value. H3 is "". ISBLANK vs empty is FALSE for that cell. COUNTBLANK will count it. A pivot that drops blanks drops the row. You did not filter. You erased the error.
  • IFERROR is wider than the bug. F2 also hides #N/A, #VALUE!, and #REF! if a later edit introduces them. You cannot see which one you hit. IFNA would hide Q3’s miss and would not hide D3’s #DIV/0!. Picking IFERROR because “IFNA left the triangle” is how divide-by-zero becomes zero.
  • The IF(ISERROR(...),"", value) form calculates the division twice and still masks every error type. It is not a narrower test.
  • A guard is different from a mask. =ARRAYFORMULA(IF(B2:B4=0,"",A2:A4/B2:B4)) blanks only the zero divisor. If A3 is text, #VALUE! still shows. That is what you want while you clean the column. IFERROR would hide the text too.
  • VLOOKUP inside the same wrapper turns a missing sku into an empty bay (O3). The next COUNTIF on column O under-counts misses. See VLOOKUP #N/A for the miss itself. Do not fix a miss by deleting it.

This is not AND/OR collapsing a spill. F2 spilled three cells. The values are wrong; the shape is right. Empty cells multiplied in an array can also show 0. That page is empty-row zeros. B3 here is a typed 0, and D3 is #DIV/0!, not a quiet zero.

Fix

Section titled “Fix”

Leave F2, H2, and O2 on the sheet. They are the bug. Keep D2 and Q3 as the unmasked pair.

Guard the case you mean. For this hours column, in D6 enter =ARRAYFORMULA(IF(B2:B4=0,"div0",A2:A4/B2:B4)). D7 (the second spill row) shows div0. D6 shows 5. D8 shows 1. The label is visible. A text hours cell would still error.

D6 selected. Formula bar shows =ARRAYFORMULA(IF(B2:B4=0,"div0",A2:A4/B2:B4)). The spill is 5, div0, 1. F3 is still 0 from IFERROR. D3 is still #DIV/0!.

Lookups: IFNA on purpose, and not inside the only copy. =ARRAYFORMULA(IFNA(VLOOKUP(M2:M4,J2:K3,2,FALSE),"missing")) shows missing for sku 999. Use that only after Q3 has taught you the tooltip. Do not use IFERROR for a lookup. A #REF! from a bad column index would become "missing" too. See IFERROR vs IFNA.

Divide-by-zero as its own page. One cell =A3/B3 is divide by zero. The array does not change the arithmetic. It only copies it.

If a spilled column is all numbers and one of them sits on a zero divisor, open the formula and look for IFERROR or ISERROR. Put the raw ARRAYFORMULA beside it until the triangle is back.