Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to average clock times | Excel Worksheet Functions | |||
average of times | Excel Discussion (Misc queries) | |||
How do I add a list of times to get an average time? | Excel Discussion (Misc queries) | |||
Formula to average certain times | Excel Discussion (Misc queries) | |||
Formula to Average times...again | Excel Discussion (Misc queries) |