Calculate average and not include zero values

[Guest]

I have a final issue with my measurements sheet. I have several columns
designed to calculate how long it has taken people to do something ie, lines
minutes lines per hr
I then have a list of people and at the bottom of this list it then
calculates the average no of lines per hr, trouble is it includes any zeroes
also in the list, so instead of giving average of say the 2 people who did
job it also includes 0 from the other 9?
The formula i have at the moment is
=SUM(IF(ISNUMBER(E5:E13),E5:E13,0))/COUNT(E5:E13) originally this was due to
#DIV/0 issues which have been sorted. if i just use =AVERAGE(E5:E13) i just
get the same answer now,
can anyone help with my problem please,
Thanks,

 

[Guest]

=SUMIF(E5:E13,">0")/COUNTIF(E5:E13,">0")
Bob Umlas
Excel MVP

 

[Guest]

K1ngy
Just thinking off the top of my head.....you could use the CountIF and set
the criteria to be greater than 0, this will then just count the values above
and ingonre all the zero values

CountIF(E5:E13,"0>")....have checked this out with a column of 5 numers
three were 1s, two were 0 and it's worked...

Hope this helps

Regards
Steve

 

[Bob Phillips]

=AVERAGE(IF(E5:E13>0,E5:E13))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi thats it thanks,
I better get some brownie points from work for this,
Steve.

 

[Guest]

Glad to have been of assistance...it just bugs the pants off you when you
can't solve the little bits

Regards
Again
Steve
(UK)