how to average times in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry58 - 05 Mar 2008 14:47 GMT

Thank you in advance for the advice! In a worksheet I would like to average a
column of times, the cells are like this:
11:30
11:20
11:17
11:32
11:39
11:10
11:01
etc.
Could someone please help with the formula? Thanks

Reply to this Message

Mike H - 05 Mar 2008 14:55 GMT

maybe

=AVERAGE(A1:A7)

Mike

> Thank you in advance for the advice! In a worksheet I would like to average a
> column of times, the cells are like this:
[quoted text clipped - 7 lines]
> etc.
> Could someone please help with the formula? Thanks

Reply to this Message

Jerry58 - 05 Mar 2008 15:33 GMT

Thanks, Mike, but that didn't work, I should have mentioned that the times
vary also. Example:
10:52
11:09
10:36
10:25
11:01
etc.
I have 2 columns with times like these, sum average gave me a credible time
(10:38) in one of the columns, but on the second one it gave me an answer of
20:10? Each column has about 20 entries.........

> maybe
>
[quoted text clipped - 13 lines]
> > etc.
> > Could someone please help with the formula? Thanks

Reply to this Message

David Biddulph - 05 Mar 2008 16:39 GMT

So what values do you think you have in the column for which you're getting
an average of 20:10 ?
(Safest to copy from your spreadsheet to here, rather than trying to
retype).

Signature

David Biddulph

> Thanks, Mike, but that didn't work, I should have mentioned that the times
> vary also. Example:
[quoted text clipped - 28 lines]
>> > etc.
>> > Could someone please help with the formula? Thanks

Reply to this Message

Jerry58 - 05 Mar 2008 18:06 GMT

Here is a copy-paste of the column that Im trying to average, they are
formatted in a time format. Sum-average gave me a result of 20:10, not what I
am looking for. These are P.M times, if that makes a difference........Thank
you
10:49
10:38
10:49
10:46
11:01

10:46
10:54
10:04
10:46
10:46
10:35
10:04
10:58
10:55
10:55
10:46
10:04
10:53
10:53
10:03
10:53
10:43
10:03
11:03
11.00
10:55
10:32

> So what values do you think you have in the column for which you're getting
> an average of 20:10 ?
[quoted text clipped - 32 lines]
> >> > etc.
> >> > Could someone please help with the formula? Thanks

Reply to this Message

David Biddulph - 05 Mar 2008 20:45 GMT

I don't understand what you mean when you say they are P.M. times. If they
were P.M. times they should either appear as 22:49 or 10:49 PM.

I also don't understand what you mean when you talk about Sum-average. SUM
is a function that adds numbers. AVERAGE is a different function that
calculated the average (or arithmentic mean). AVERAGE is the function which
Mike H suggested, so I don't know what you are doing with SUM?
[If you don't understand what an Excel function does, look it up in Excel
help (unless the function is DATEDIF, of course)].

Forgetting that, the answer I get using the AVERAGE function on your numbers
is not 20:10 but 20:03. The reason why it isn't what you might expect is
that one of your times isn't 11:00 with a semi-colon but 11.00 with a
decimal point. As Excel times and dates are stored in units of 1 day, 11.00
is equivalent to 11 days, or 264 hours, which has a significant effect on
your average.

Lesson 1:
If you get an unexpected answer from Excel, it's probably because you've
asked it the wrong question.

Signature

David Biddulph

> Here is a copy-paste of the column that Im trying to average, they are
> formatted in a time format. Sum-average gave me a result of 20:10, not
[quoted text clipped - 70 lines]
>> >> > etc.
>> >> > Could someone please help with the formula? Thanks

Reply to this Message

Jerry58 - 06 Mar 2008 14:27 GMT

David, thank you for the response, I am a newbie to excel. I had the times
formatted wrong etc. I followed your suggestions and the average function
worked fine.

> I don't understand what you mean when you say they are P.M. times. If they
> were P.M. times they should either appear as 22:49 or 10:49 PM.
[quoted text clipped - 90 lines]
> >> >> > etc.
> >> >> > Could someone please help with the formula? Thanks

Reply to this Message

David Biddulph - 06 Mar 2008 18:23 GMT

Glad to hear that it did the trick.
--
David Biddulph

> David, thank you for the response, I am a newbie to excel. I had the times
> formatted wrong etc. I followed your suggestions and the average function
[quoted text clipped - 101 lines]
>> >> >> > etc.
>> >> >> > Could someone please help with the formula? Thanks

Reply to this Message

Jerry58 - 05 Mar 2008 18:01 GMT

> Thanks, Mike, but that didn't work, I should have mentioned that the times
> vary also. Example:
[quoted text clipped - 25 lines]
> > > etc.
> > > Could someone please help with the formula? Thanks

Reply to this Message