Calculations resulting in #DIV/0!
 

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

Calculations resulting in #DIV/0!
 

=if(countif(a1:a6,"0")=0,"",sum(a1:a6)/countif(a1:a6,"0"))
--
Gary''s Student - gsnu200770

Calculations resulting in #DIV/0!
 

=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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Debbie" wrote in message
...
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

Calculations resulting in #DIV/0!
 

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

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

"Bernard Liengme" wrote in message
...
=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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Debbie" wrote in message
...
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

Calculations resulting in #DIV/0!
 

"Bernard Liengme" wrote...
....
=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

Calculations resulting in #DIV/0!
 

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

Much appreciated!
Debbie

"Harlan Grove" wrote:

"Bernard Liengme" wrote...
....
=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

Calculations resulting in #DIV/0!
 

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

"Harlan Grove" wrote in message
...
"Bernard Liengme" wrote...
...
=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