Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default how to average times in a column

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default how to average times in a column

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default how to average times in a column

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.........

"Mike H" wrote:

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default how to average times in a column

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

"Jerry58" wrote in message
...
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.........

"Mike H" wrote:

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default how to average times in a column



"Jerry58" wrote:

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.........

"Mike H" wrote:

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default how to average times in a column

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


"David Biddulph" wrote:

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

"Jerry58" wrote in message
...
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.........

"Mike H" wrote:

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default how to average times in a column

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" wrote in message
...
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


"David Biddulph" wrote:

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

"Jerry58" wrote in message
...
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.........

"Mike H" wrote:

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default how to average times in a column

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" wrote:

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" wrote in message
...
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


"David Biddulph" wrote:

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

"Jerry58" wrote in message
...
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.........

"Mike H" wrote:

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default how to average times in a column

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

"Jerry58" wrote in message
...
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" wrote:

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" wrote in message
...
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


"David Biddulph" wrote:

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

"Jerry58" wrote in message
...
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.........

"Mike H" wrote:

maybe

=AVERAGE(A1:A7)


Mike

"Jerry58" wrote:

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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to average clock times Tall Texan Excel Worksheet Functions 1 February 5th 08 04:59 PM
average of times m greenly Excel Discussion (Misc queries) 4 March 23rd 07 03:37 PM
How do I add a list of times to get an average time? Matt75 Excel Discussion (Misc queries) 1 January 11th 06 04:07 PM
Formula to average certain times Denise Excel Discussion (Misc queries) 5 July 18th 05 01:02 PM
Formula to Average times...again Denise Excel Discussion (Misc queries) 1 July 15th 05 08:26 PM


All times are GMT +1. The time now is 05:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"