Adding time [t]:mm:ss

[Jan G. Thorstensen]

Hi.
I try to figure out the average time spent in a range but cant figure out
how.

This is how it looks like:

00:00:10
00:00:06
00:00:00
00:00:04
00:00:04
------------
=AVERAGE(A2:A6)
===============
Here is a total of 24 seconds.
These seconds should be divided with 4 and return the answer; 00:00:06.

The problem is:
1) One row does not contain any hours, minutes or seconds and should not be
counted.
2) Even when there is no figures in that particular row, the answer returned
is wrong?

Anybody have a suggestion?

Thanks in advance for your help!

Jan

 

[Biff]

Hi!

Try this:

Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF(A2:A6>0,A2:A6))

Format the cell as h:mm:ss

Biff

 

[Jan G. Thorstensen]

Thank you so much. That did the trick.

Regards
Jan

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thank you so much. That did the trick.

Regards
Jan

 

[Jan G. Thorstensen]

Hi, again. One more problem with this.
When I try to calculate with more arguments,
it fails?
Say I have different ranges I want to include.

=AVERAGE(IF(A2:A6,A10:A14>0,A2:A6,A10:A14))
or
=AVERAGE(IF((A2:A6,A10:A14>0),(A2:A6,A10:A14)))

Now, it returns an error? It say the formula contains too
many arguments. How can I avoid this or how should the
formula look like?

Thank you for your help!

Regards
Jan.

PS I am using the key combination of CTRL,SHIFT,ENTER.

 

[Biff]

Hi!

You can't use multiple disjointed ranges in that manner.

What do you have in the cells that are between the 2 ranges? A7, A8 and A9?
If these cells are empty or have text in them then you can just include them
in the reference as a contiguous range like:

=AVERAGE(IF(A2:A14>0,A2:A14))

Text and empty cells will be ignored in the formula.

If the cells between the ranges contain numbers then try one of these. The
first one is an array. (array entered - CTRL,SHIFT,ENTER)

=AVERAGE(IF(CHOOSE({1,2},A2:A6,A10:A14)>0,CHOOSE({1,2},A2:A6,A10:A14)))

Or, normally entered (with just ENTER):

=(SUMIF(A2:A6,">0")+SUMIF(A10:A14,">0"))/(COUNTIF(A2:A6,">0")+COUNTIF(A10:A14,">0"))

Biff

 

[Jan T.]

Thank you again.
Awesome what is possible to do with formulas.

Jan

 

[Biff]

You're welcome!

Biff

Thank you again.
Awesome what is possible to do with formulas.

Jan