Third Shift (11pm - 7am) checks the status of an item each night. I want to get the average of the time the check is completed. I have a log with each day and military time of when it was checked. Simply averaging the times doesn't work because excel treats the time like a number (or appears to)...
=Average(01:56,0:19,23:59,2:20) gives a result of: 7:08
Any suggestions? Thanks in advance.
What would you expect as a result of averaging those times you have in your sample?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
cant see how you can average a time from that info , what do you expect it to say?
is it the average time it took to complete each check ? if so you need a start and end time.
otherwise as you say it will simply add those times up divide by number of them and give an average of all those hours and minutes.
the other thing is the average sees any thing before midnight as at the other end of the same day ,if you put in all times past midnight the average check time is calculated
Last edited by martindwilson; 07-18-2008 at 02:33 PM.
I guess I have to look at the dates in conjunction with the times. In the above example, I was hoping to get an average time of around 1am...
I wouldn't need the time frame, just the start time. I'll research how to calculate average start times...
i had a rethink and came up with this. only good for times between 23:00 and 07:00(range must match in all formulas or you'll get #value errors)
Last edited by martindwilson; 07-18-2008 at 03:42 PM.
Assuming your times are all between 23:00 and 07:00 then this will give an average, with times in A1:A4, blanks ignored
=MOD(SUMPRODUCT(MOD(A1:A4+"1:00",1)*(A1:A4<>""))/COUNT(A1:A4)-"1:00",1)
or an array formula which needs to be confirmed with CTRL+SHIFT+ENTER
=MOD(AVERAGE(IF(A1:A4<>"",MOD(A1:A4+1/2,1)))-1/2,1)
Last edited by daddylonglegs; 07-18-2008 at 03:48 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks