Average outage time?

C

C.S.Harris

I have a sheet that shows when an item was logged out (i.e. maintenence) and
when it was logged back in. The sheet calculates how long each item was
logged out. Now I want to calculate the average outage time for all items
listed.
I have tried the following functions, the first one errors becase of zero
values, the second one is supposed to ignore zero values, but it just doesn't
work.
=AVERAGE(J114:J164)
=AVERAGE(J114:J164)+IF(J114:J164<>0,"","")

Anything will help!!
 
E

Elkar

Perhaps this will work:

=SUMIF(J114:J164,">0",J114:J164)/COUNTIF(J114:J164,">0")

HTH,
Elkar
 
B

Bob Phillips

=AVERAGE(IF(J114:J164<>0,J114:J164))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
C

C.S.Harris

Bob Phillips said:
=AVERAGE(IF(J114:J164<>0,J114:J164))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Click to expand...

I tried that one also, it seemd like it should be working. I think the
problem is with my time formatting. For J114-J164, the formula output is
[hh]:mm and all cells that don't have info have 00:00, which I can't hide.
When I use the formula, I get #DIV/0! or #VALUE!.
Anything? Thanks.
 
B

Bob Phillips

It works fine with time. Your cells must be text rather than numeric is all
I can an think.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

C.S.Harris said:
Bob Phillips said:
=AVERAGE(IF(J114:J164<>0,J114:J164))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Click to expand...

I tried that one also, it seemd like it should be working. I think the
problem is with my time formatting. For J114-J164, the formula output is
[hh]:mm and all cells that don't have info have 00:00, which I can't hide.
When I use the formula, I get #DIV/0! or #VALUE!.
Anything? Thanks.
Click to expand...
 
Top