Calculations resulting in #DIV/0!

[Debbie]

The following example is:

a1 0%
a2 0%
a3 0%
a4 0%
a5 0%
a6 0%

Here's what I have:
=sum(a1:a6)/countif(a1:a6,">0")

I have multiple cells using this formual that have numbers and function fine.
But, with no numbers to calculate against I'm getting the #DIV/0!.
How can I fix this so I don't have the #DIV/0!?

I've been reading about =IF but I've tried numerous ways and have come up
with nothing working.

Thanks for your help.

Debbie

 

[Gary''s Student]

=if(countif(a1:a6,">0")=0,"",sum(a1:a6)/countif(a1:a6,">0"))

 

[Bernard Liengme]

=IF(COUNTIF(A1:A6,">0",sum(a1:a6)/countif(a1:a6,">0"),"Not data")
or
=IF(ISERRROR(sum(a1:a6)/countif(a1:a6,">0")),"Problem",sum(a1:a6)/countif(a1:a6,">0"))
best wishes

 

[Bernard Liengme]

Typo
=IF(COUNTIF(A1:A6,">0")>0,sum(a1:a6)/countif(a1:a6,">0"),"Not data")

 

[Harlan Grove]

=IF(COUNTIF(A1:A6,">0")>0,sum(a1:a6)/countif(a1:a6,">0"),"Not data")

....

An alternative approach,

=SUM(A1:A6)/MAX(1,COUNTIF(A1:A6,">0"))

which would return 0 if none of the cells in A1:A6 contained a
positive number, but since the COUNTIF criteria implies only positive
values would be valid, then the average of any set of positive numbers
would be positive, so a 0 result would be an unambiguous indication
that the average wasn't valid. Possible at this point to use a custom
number format, e.g.,

[<=0]"no valid data";General

 

[Debbie]

Thanks everyone, for your quick responses.
This helps me complete my calculations.

Much appreciated!
Debbie

=IF(COUNTIF(A1:A6,">0")>0,sum(a1:a6)/countif(a1:a6,">0"),"Not data")

....

An alternative approach,

=SUM(A1:A6)/MAX(1,COUNTIF(A1:A6,">0"))

which would return 0 if none of the cells in A1:A6 contained a
positive number, but since the COUNTIF criteria implies only positive
values would be valid, then the average of any set of positive numbers
would be positive, so a 0 result would be an unambiguous indication
that the average wasn't valid. Possible at this point to use a custom
number format, e.g.,

[<=0]"no valid data";General

 

[Bernard Liengme]

Very nice!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

=IF(COUNTIF(A1:A6,">0")>0,sum(a1:a6)/countif(a1:a6,">0"),"Not data")

...

An alternative approach,

=SUM(A1:A6)/MAX(1,COUNTIF(A1:A6,">0"))

which would return 0 if none of the cells in A1:A6 contained a
positive number, but since the COUNTIF criteria implies only positive
values would be valid, then the average of any set of positive numbers
would be positive, so a 0 result would be an unambiguous indication
that the average wasn't valid. Possible at this point to use a custom
number format, e.g.,

[<=0]"no valid data";General