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