Average outage time?

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!!

Perhaps this will work:

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

HTH,
Elkar


"C.S.Harris" wrote:

> 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!!

=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.S.Harris" <[email protected]> wrote in message
news:[email protected]...
> 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!!

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

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.

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" <[email protected]> wrote in message
news:[email protected]...
> "Bob Phillips" wrote:
> > =AVERAGE(IF(J114:J164<>0,J114:J164))
> > which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
> > just Enter.
>
> 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.