Averaging time values

  • Thread starter Thread starter John Oliver
  • Start date Start date
J

John Oliver

Now that I have my input working, I want to be able to average the
contents of a given range of cells. So, given:

00:00:10
00:00:30
00:01:25
00:00:16
00:02:37
00:00:08
00:00:02

I want to wind up with 00:00:44

Just using =AVERAGE(F4:F27) doesn't do the trick. One issue is probably
that "empty" cells don't appear as 0 but 12:00:00 AM
 
Now that I have my input working, I want to be able to average the
contents of a given range of cells. So, given:

00:00:10
00:00:30
00:01:25
00:00:16
00:02:37
00:00:08
00:00:02

I want to wind up with 00:00:44

Just using =AVERAGE(F4:F27) doesn't do the trick. One issue is probably
that "empty" cells don't appear as 0 but 12:00:00 AM
Click to expand...

Average ignores "empty" cells. But if they are showing up as 12:00:00 AM then
those cells contain a '0' and are not truly empty. They must be the result of
a formula.

If you use the *array-entered* formula =AVERAGE(IF(F4:F27<>0,F4:F27)) then any
0's will be ignored.

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
 
Average ignores "empty" cells. But if they are showing up as 12:00:00 AM then
those cells contain a '0' and are not truly empty. They must be the result of
a formula.

If you use the *array-entered* formula =AVERAGE(IF(F4:F27<>0,F4:F27)) then any
0's will be ignored.

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.
Click to expand...

Thanks... will try in the AM.
 
Ron Rosenfeld wrote
If you use the *array-entered* formula =AVERAGE(IF(F4:F27<>0,F4:F27))
then any 0's will be ignored.
Click to expand...

Sadly, at least w/Excel2000, this won't work in merged cells.
 
Ron Rosenfeld wrote


Sadly, at least w/Excel2000, this won't work in merged cells.
Click to expand...

It seems to work OK in XL2002. Is that a documented problem in 2000, or is
there a possibility you could be dealing with another issue?


--ron
 
Average ignores "empty" cells. But if they are showing up as 12:00:00 AM then
those cells contain a '0' and are not truly empty. They must be the result of
a formula.
Click to expand...

....but they're not. They're simply a time with no value entered yet.
Which is probably why...
If you use the *array-entered* formula =AVERAGE(IF(F4:F27<>0,F4:F27)) then any
0's will be ignored.
Click to expand...

....this results in #VALUE!

:-(

Next?
 
You are not conveying all of the relevant information.

You did *array-enter* the formula? <ctrl><shift><enter>. (but that won't
cause the error message you are reporting).
Click to expand...

I had a brain freeze and thought you had been saying to ctrl-shift the
selection. I entered the formula with ctrl-shift and it works now!
Thank you!
 
Ron Rosenfeld wrote
It seems to work OK in XL2002. Is that a documented problem in 2000,
or is there a possibility you could be dealing with another issue?
Click to expand...

Can't say if it's a 2000 problem or not. Works as advertized if array
entered into a single cell, otherwise, I get "Array formulas are not valid
in merged cells". I can only conclude it's not supported.
 
I had a brain freeze and thought you had been saying to ctrl-shift the
selection. I entered the formula with ctrl-shift and it works now!
Thank you!
Click to expand...

You're welcome. Glad it is working for you.


--ron
 
Back
Top