Google Sheets: Avoid #DIV/0! In ArrayFormula()

user_190925 0
Viewed 657

I'm looking for the way to avoid the #DIV/0! error in an ArrayFormula. Originally the cell formula was:

=arrayformula(F2:F3406/G2:G3406)

I've since tried:

=arrayformula(iferror(F2:F3406/G2:G3406,''))
=iferror(arrayformula(F2:F3406/G2:G3406),'')

However, these both give #ERROR!, with the comment 'Formula parse error'.

How should this be done?

array-formulas divide-by-zero google-sheets
2 Answers

use:

=ARRAYFORMULA(IFERROR(1/(1/(F2:F3406/G2:G3406))))

or:

=ARRAYFORMULA(IFERROR(1/(1/(F2:F3406/G2:G3406)), 0))
user_56326290

An easier solution would be : =ARRAYFORMULA(AVERAGE(IFERROR(Range1/Range2)))

That is in this example =ARRAYFORMULA(AVERAGE(IFERROR(F2:F3406/G2:G3406)))

user_159074250
Related