Incorrect arithmetic when computing Average (hours:minutes)

[Guest]

I'm doing analysis of time data, e.g. 1:21 represents 1 hour, 21 minutes.

1:21
1:45
1:55
2:16
1:49
------
=AVERAGE(V7:V11) Excel computes the correct answer, 1:49

But elsewhere I get the wrong answer. This data averages to 1:20 (1 hour,
20 minutes). But excel computes 10:56 (10 hours, 56 minutes) instead.

0:47
1:09
2:00
1:42
1:02

 

[Frank Kabel]

Hi
are you sure all cells are formated correctly. Also check in an
adjacent column the formula
=A1>1
and copy this down for all rows. Should always show FALSE

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag
news:[email protected]...

 

[Ron Rosenfeld]

On Fri, 1 Oct 2004 12:49:02 -0700, Tim in Michigan <Tim in

0:47
1:09
2:00
1:42
1:02
------
=AVERAGE(AC7:AC11) Excel displays 10:56 (10 hours, 56 minutes) instead
of the correct answer (1:20 (1 hour, 20 minutes).

Can anyone tell me what I am doing wrong?

First, I get 1:24:30 (1 Hr, 24 min, 30 sec) for an average using those
numbers.

I suspect that the numbers are not what they appear.

Ensure that the numbers in AC7:AC11 are formatted as [h]:mm and see what it
looks like.


--ron

 

[Jerry W. Lewis]

Excel times are actually date/time values. Format to show the date as
well as the time, and you will find that three of your values are from
one day and the other two are from the next day.

The integer part of a date/time value is the number of days since the
beginning of 1900, the fractional part of a date time value is the
fraction of 24 hours represented by the time. Thus if all times were
from the same date (say d), you would have
0:47 = d + 47/1440
1:09 = d + 69/1440
2:00 = d + 120/1440
1:42 = d + 102/1440
1:02 = d + 62/1440
which has an average of d + 80/1440 which when formatted as a time
displays as 1:20. If two of the times are from the following day (d+1),
then the average is d + 2/5 + 80/1440 = d + 656/1440 which when
formatted as a time displays as 10:56.

Jerry