Forum Home
Press F1
Thread ID: 55439	2005-03-10 09:03:00	Averaging Excel 'time cells'!	Brain_Cell_Anon (261)	Press F1

Post ID	Timestamp	Content	User
332787	2005-03-10 09:03:00	Watcha Gang! Here's one that you may find interesting. :illogical I am trying to do an Excel spreadsheet with depart, arrive, unload, reload, depart and arrive back times each cell is formatted in the 24 hr clock (00:00 to 24:00). Now that's the easy part! Ready? :stare: Is it possible to do an AVERAGE of times? So for instance, to be able to average all the unload times so that if the times were heading towards 04:00 when they should be 03:00 or the latest 03:30 can I show this? I'm getting grief from my boss because he says I should be back in the depot by a certain time. I agree. But it appears, on average, that the unload times and reload times are getting later!!!! I'm sure one of you brainy buggers can help, a? Many regards, The Cell :D	Brain_Cell_Anon (261)
332788	2005-03-10 19:53:00	=AVERAGE(startcell:endcell) will give an average of the times. You could also graph the data, which would readily show if there is a trend.	rad_s4 (7401)
332789	2005-03-11 07:11:00	You could add a column for date as well so you can track which days took longer, specially if you want to graph the results. This could be of interest if some days are regulally take longer than others. Have you managed to get the averaging sorted out?	craigb (4884)
332790	2005-03-11 08:25:00	rad_s4, thanks for that. I think the word... Doh! comes to mind! :D craigb. Hmm! Sounds very interesting, but I have to ask, do you mean the date in the same cell as the time? Any pointers would be good! Regards, The Cell	Brain_Cell_Anon (261)
332791	2005-03-11 08:44:00	Be better in a seperate column. You could do it in same cell but would be a bit long-winded having date and time together.	craigb (4884)
332792	2005-03-17 08:53:00	Ok! got that working thank you. Just one small hitch. I tend to arrive around 23:50 but a couple of times I've arrived after midnight (00:01). This plays havoc with the average, as there is now not only a '23' on there but a '00' as well. I don't think this can be got around, but I'm willing to bet one of bright sparks may think of something!! :D Regards, The Cell	Brain_Cell_Anon (261)
332793	2005-03-17 09:15:00	Hi BCA. Interesting. Years ago when such facilities were just coming on line, we tried to sort out our calls for service on response times and average times of calls and such like. I can remember that we had an awful lot of calls at an average time slot of 3.15 am on Sunday mornings when, if fact, there never was a call within hours of that time. We spent forever on it and finally gave it away. I seem to recall that we proved that you could not have entries for 00:00 and for 24:00, they being one and the same time. The latest time we accepted was 23:59. Anyway, good luck. :cool:	Scouse (83)
332794	2005-03-17 09:36:00	Hi, your better off with dates and times within the same cell. Excel treats dates and times as numbers behind the scenes. Whole numbers equate to days with decimals equating to a time. For example, 0.50 means midday with no specific date. When you use 00:30 this means 12:30AM so if you have other times with 23:30 then they are 23 hours apart (rather than the 1 hour you want) because the day isnt taken into consideration. Enter the times like this... 17/03/2005 23:00 17/03/2005 23:30 17/03/2005 23:15 18/03/2005 0:05 Presuming these cells are in C1:C4 then the formula =AVERAGE(C1:C4) will return 23:27. To ensure this works properly, include the date 17/3/05 with all times before midnight, and the date 18/3/05 with all times after midnight. Its important thats theres only one day between all the times. Have another column that stores the actual date for display purposes. After completing the times you can format the cells to only display hours and minutes by seleting Format|Cells|Custom Type then entering H:MM. For more info on how Excel calculates times see here... www.pearson.com	Parry (5696)
1