How to hide "#DIV/0" error

[jaedmar]

Here is a formula that I am using in cells:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!b15"),"F",INDIRECT("'"&sheetlist&"'!b8")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!b15"),"F"))

One thing that I have noticed was that if there were not yet any B15 cells in which "F" was entered, the formula cell returns an error, #DIV/0, apparently because it is not possible to divide by zero. I am not too concerned with that error, since it will go away once data is entered.
However, to keep the sheet looking neater, how do I alter this formula to not show that error, but rather just leave the cell empty?

 

[barry houdini]

Try this version to return a zero instead of an error

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!b15"),"F",INDIRECT("'"&sheetlist&"'!b8")))/MAX(1,SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!b15"),"F")))

then you could format the cell to show zero as a blank

....or if you have Excel 2007 or later...

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!b15"),"F",INDIRECT("'"&sheetlist&"'!b8")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!b15"),"F")),"")

or in Excel 2003 or earlier...

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!b15"),"F")),SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!b15"),"F",INDIRECT("'"&sheetlist&"'!b8")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!b15"),"F")),"")

 

[Zack Barresse]

Why don't you just surround in an IF() function, something like...

=IF(LEN(B15)=0,nothing,yourFormula)

HTH

 

[jaedmar]

I used the IFERROR and that worked perfect on most of my cells.

Any clue what is wrong with the following formula?

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!m8"),">=20<25",INDIRECT("'"&sheetlist&"'!m9")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!m8"),">=20<25")),"")

My expectation is for it to provide an average of all M9 cells if M8 cells contain a number between 20 and 25 (actually equal or greater to 20 but less than 25).
I have verified that there is relevant data in the referenced cells.
The result I actually get is a blank cell.

 

[jasonb75]

Any clue what is wrong with the following formula?

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!m8"),">=20<25",INDIRECT("'"&sheetlist&"'!m9")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!m8"),">=20<25")),"")

That doesn't evaluate correctly, you need to define each criteria on its own

As an alternative, try

=IFERROR(average(if(median(INDIRECT("'"&sheetlist&"'!m8"),20,25)=INDIRECT("'"&sheetlist&"'!m8"),INDIRECT("'"&sheetlist&"'!m9"))),"")

Confirmed as array formula using shift ctrl enter.

 

[barry houdini]

You can't use "between" type criteria like ">=20<25", I think you'd have to use SUMIFS and COUNTIFS and use two separate conditions, one for ">=20" and one for "<25", i.e.

=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&sheetlist&"'!m9"),INDIRECT("'"&sheetlist&"'!m8"),">=20",INDIRECT("'"&sheetlist&"'!m8"),"<25"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&sheetlist&"'!m8"),">=20",INDIRECT("'"&sheetlist&"'!m8"),"<25")),"")

 

[barry houdini]

=IFERROR(average(if(median(INDIRECT("'"&sheetlist&"'!m8"),20,25)=INDIRECT("'"&sheetlist&"'!m8"),INDIRECT("'"&sheetlist&"'!m9"))),"")

Hello Jason,

Interesting use of MEDIAN......but I don't think you can use that approach here. Bear in mind that sheetlist is a list of several sheets (I assume) but using that with MEDIAN won't return an array, as required, but a single MEDIAN. For an array formula with AVERAGE I think you'd need this formula

=IFERROR(AVERAGE(IF(N(INDIRECT("'"&sheetlist&"'!m8"))>=20,IF(N(INDIRECT("'"&sheetlist&"'!m8"))<25,N(INDIRECT("'"&sheetlist&"'!m9"))))),"")

confirmed with CTRL+SHIFT+ENTER

 

[jasonb75]

Thanks for the correction Barry, I was almost certain that I had used that method on arrays before, I had misread the use of the indirect function in this case, but even with a single sheet array it still fails.

=IF("posting untested formula", )