how to average times in a column

  • Thread starter Thread starter Jerry58
  • Start date Start date
J

Jerry58

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
 
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.........
 
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).
 
Jerry58 said:
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.........
Click to expand...
 
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
 
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.
 
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.

David Biddulph said:
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.
--
David Biddulph

Jerry58 said:
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
Click to expand...
Click to expand...
 
Glad to hear that it did the trick.
--
David Biddulph

Jerry58 said:
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.

David Biddulph said:
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.
--
David Biddulph

Jerry58 said:
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 ?
(Safest to copy from your spreadsheet to here, rather than trying to
retype).
--
David Biddulph

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

=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:
11:30
11:20
11:17
11:32
11:39
11:10
11:01
etc.
Could someone please help with the formula? Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average time between 2 dates 2
Average If... 12
Average Unique 7
Modifying a complex date formual to count months 4
Peak call times/hours 2
time_report_date-wise_employee-wise 6
WCG Stats Saturday 06 March 2021 2
Averaging a column that contains text 4

Back
Top