#div/0! error returned from function

[jg53]

I have a function I am using as follows:

=SUM(B10:B13)/COUNTIF(B10:B13,"<>0")-36

Sometimes the sum field is a 0 and this is causing #div/0! error. No
sure how to fix this. Tried using
=SUMif(B10:B13,"=0")/COUNTIF(B10:B13,"<>0")-36
but couldn't make that work either. Can anyone help pleas

 

[Frank Kabel]

Hi
one way:
=IF(COUNTIF(B10:B13,"<>0"),SUM(B10:B13)/COUNTIF(B10:B13,"<>0")-36,"")

 

[Don Guillett]

Try this ARRAY formula (must be entered with ctrl+shift+enter vs just enter)
=AVERAGE(IF(b10:b13:>0,b10:b13))

 

[Aladin Akyurek]

=SUM(B10:B13)/MAX(1,COUNT(B10-B13)-COUNTIF(B10:B13,0))-36

 

[hgrove]

Don Guillett wrote...

Try this ARRAY formula (must be entered with ctrl+shift+enter vs
just enter)
=AVERAGE(IF(b10:b13:>0,b10:b13))

...

Testing is good. If all cells in this range are nonpositive, the
formula is equivalent to

=AVERAGE({FALSE;FALSE;FALSE;FALSE})

which returns (drum roll) #DIV/0!

 

[hgrove]

Aladin Akyurek wrote...

=SUM(B10:B13)/MAX(1,COUNT(B10-B13)-COUNTIF(B10:B13,0))-36

...

Typo. Should be

=SUM(B10:B13)/MAX(1,COUNT(B10:B13)-COUNTIF(B10:B13,0))-36

Also, wrt using COUNT(x)-COUNTIF(x,0), I'd guess you were trying t
protect against including text cells in the count, but by the myster
of MSFT QA COUNTIF(x,"<>0") doesn't include any text cells in its coun
(at least not in XL97), so the ideal formula would appear to be

=SUM(B10:B13)/MAX(1,COUNTIF(B10:B13,"<>0"))-36

Note that if the OP's observations are all nonnegative, it'd be eve
better to use

=SUM(x)/MAX(1,COUNTIF(x,">0"))-36

So given A1:A2 containing

0
="0"

COUNTIF(A1:A2,0), COUNTIF(A1:A2,"0") and COUNTIF(A1:A2,"0") all retur
2, but COUNTIF(A1:A2,"<>0") returns 1.

Ain't Excel wonderful?!

[Digression: it's not as if MSFT couldn't have made COUNTIF and SUMI
criteria orthogonal, i.e., for single cell x if COUNTIF(x,"=y") were
then COUNTIF(x,"<>y") would always be 0, but it would have require
adding explicit double quotes around text in the RHS of criteri
argument comparison operators, so

COUNTIF(x,"=""y""")

instead of

COUNTIF(x,"=y")

I know they decided in favor of usability, but this means there's n
way to distinguish between numeric 0 and text "0" in equalit
criteria.

 

[Don Guillett]

OP did say "sometimes"