How to average times in Number's '08

With date_time values in column B, entering in cell F2 the formula

=AVERAGE(B)

does the job if your datas are coherent.

The behaviour of date_time cells was described hundreds of time.

A date_time cell always contain a date component.
If you didn't type one, the app use the date of the inserting day.

So, if you inserted your values at different days, you may have in the column:

1 janv. 2010 00:01:30
3 janv. 2010 00:01:15
8 janv. 2010 00:00:58

and for sure, averaging them, the result would be 'surprising' but perfectly excat.

The clean soluce is simple.
In column C, insert the formula :
=TIMEVALUE(B)
in cell D2 insert the formula :
=AVERAGE(C) which returns a decimal value
or
=TIME(0,0,AVERAGE(C) 24*6060) which returns a date_time value.

Yvan KOENIG (VALLAURIS, France) mercredi 31 mars 2010 08:12:45

Here is an alternate way to treat the problem.



Time values where inserted at different dates in column B.
So, il we take care to display date & time, we may see that they are really different.
In B10 (footer row), the formula is =AVERAGE (B)
The result is perfectly correct but is not the one which you wished.

In cells of column C I inserted the formula :
=TIME(0,0,TIMEVALUE(B) 24*6060)
which normalize the date_time so that the date component is the same for every cells.
In C10 (footer row), the formula is =AVERAGE (C)
The result is perfectly correct and is the one which you wished.
To be complete, in column D I inserted the formula :
=TIMEVALUE(B)
In D10 (footer row), the formula is =AVERAGE (D)
The result is perfectly correct but I guess that at first read it's not clear.
So,
In column E I inserted again the formula :
=TIMEVALUE(B)
This time, in E10, the formula is:
=TIME(0,0,AVERAGE(E) 24*6060)

Of course, I choose to display the date component to be clear.
You may ask the cells to *display only the time component* but you must remember that the *date one always exists*.

My own tip is :
when a result upon date_time values is not the expected one, hurry up, *ask Numbers to display the date and the time components in the cells referenced by the formula*.

Yvan KOENIG (VALLAURIS, France) mercredi 31 mars 2010 09:57:44

It seems that you didn't read carefully.
When you enter a time value, Numbers stores always a complete date_time value.
If you enter one time on 2010/03/01 it's this date which is stored
If you enter one time on 2010/03/10 it's this date which is stored
If you enter one time on 2010/03/20 it's this date which is stored
This is what I illustrated and treated.

You are forced to use this scheme if you want correct recort.
Try, for just a minute to display the complete date_time in the cells storing your time values.

Yvan KOENIG (VALLAURIS, France) jeudi 1 avril 2010 07:42:44