| Forum Home | ||||
| PC World Chat | ||||
| Thread ID: 131253 | 2013-04-29 20:18:00 | Averages for time | rob_on_guitar (4196) | PC World Chat |
| Post ID | Timestamp | Content | User | ||
| 1338862 | 2013-04-29 20:18:00 | Hi guys, complete brain stoppage, I'm doing something wrong.... trying to find averages of time.... I have around a dozen times (ranging from minutes to hours e.g 22mins, 3.5hours, 12 mins etc etc) What would be the formula to use to work the averages? |
rob_on_guitar (4196) | ||
| 1338863 | 2013-04-29 21:15:00 | Convert all times to minutes, add together, divide by number of time periods, then divide by 60 to convert back to Hours and minutes. | R2x1 (4628) | ||
| 1338864 | 2013-04-29 22:01:00 | Convert all times to minutes, add together, divide by number of time periods, then divide by 60 to convert back to Hours and minutes. +1 |
Nick G (16709) | ||
| 1338865 | 2013-04-29 22:21:00 | Excel can do this easily. it's just the "AVERAGE" function, such as "=AVERAGE(A1:A100)" Enter times like "1:45" Note "AVERAGE" will ignore blank values to convert the time, such as 1:20:30, to minutes, such as 80.5, use this formula =(HOUR(B1)*60)+(MINUTE(B1))+(SECOND(B1)/60) |
kingdragonfly (309) | ||
| 1338866 | 2013-04-29 23:11:00 | Cheers guys | rob_on_guitar (4196) | ||
| 1338867 | 2013-04-29 23:48:00 | Excel can do this easily. it's just the "AVERAGE" function, such as "=AVERAGE(A1:A100)" Enter times like "1:45" Note "AVERAGE" will ignore blank values to convert the time, such as 1:20:30, to minutes, such as 80.5, use this formula =(HOUR(B1)*60)+(MINUTE(B1))+(SECOND(B1)/60) You might be able to do the same thing just by formatting cells with h:mm - Excel will use decimal numbers internally. |
pcuser42 (130) | ||
| 1338868 | 2013-04-30 03:19:00 | You're right pcuser42. In Excel, 1 unit = 24 hours enter 1, 2, 3 in cells and format it as a date, you'll see 1/01/1900 2/01/1900 3/01/1900 depending on your PC's locale setting (d/mm/yyyy in NZ) "2013-04-30 2:08 p.m." = 41,394.59. In other words 41,394 days since 0/1/1900 (not a typo; it's too confusing to explain here) So it gets more complex if you enter date and time. As mentioned, not a problem for rob_on_guitar |
kingdragonfly (309) | ||
| 1338869 | 2013-04-30 03:40:00 | In other words 41,394 days since 0/1/1900 (not a typo; it's too confusing to explain here) On a slight tangent, that might be related to the fact that older spreadsheet software treated 1900 as a leap year even though it isn't, and Excel does for compatibility reasons. |
pcuser42 (130) | ||
| 1 | |||||