How do I get a 0 to show in a cell when dividing by zero using an array formula

Question

How do I get a 0 to show in a cell when dividing by zero using an array formula

I am using the following array formula {=(SUM(IF($H$8:$H$1772&$I$8:$I$1772&$O$8:$O$1772="RDD"&"BW"&"OK",1,0))/U1785)} and some of the cells it references do not have any values in them at this time but will at a later date, therefore I am currently getting #DIV/0! in some of these cells where the answer should be. I would like it to show a zero in these cells rather than the  #DIV/0!

Any help will be appreciated.

Thanks

Explanation / Answer

Hi,

I would use this non-array formula

=SUMPRODUCT(--(H8:H1772&I8:I1772&O8:O1772="RDDBWOK"))/U1785

The only way this will return a #DIV/0! error is if U1785 is empty and to eliminate that you can use IFERROR.

=IFERROR(SUMPRODUCT(--(H8:H1772&I8:I1772&O8:O1772="RDDBWOK"))/U1785,0)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.