T
tillyosu
How can I make excel ignore a #DIV/0! error when averaging a range o
cells??
cells??
G
Guest
An if statement that does the averaging calc only when it is not zero.
B
Bob Phillips
Something like
=IF(B1=0,"",A1/B1)
--
HTH
RP
(remove nothere from the email address if mailing direct)
=IF(B1=0,"",A1/B1)
--
HTH
RP
(remove nothere from the email address if mailing direct)
H
Harlan Grove
tillyosu wrote...
Meaning something like AVERAGE(B5
10) returns #DIV/0! ? That only
happens when there are no numeric values in the range or when one or
more of the cells in the range evaluates to #DIV/0! . You probably
shouldn't ignore the latter.
As for the former, it depends on what you want to show, but the generic
approach is
=IF(COUNT(B510),AVERAGE(B510),"")
Meaning something like AVERAGE(B5
10) returns #DIV/0! ? That onlyhappens when there are no numeric values in the range or when one or
more of the cells in the range evaluates to #DIV/0! . You probably
shouldn't ignore the latter.
As for the former, it depends on what you want to show, but the generic
approach is
=IF(COUNT(B5
10),AVERAGE(B510),"")