averaging time 1

[JohnnyD]

I am creating a spreadsheet that has times for unloading freight. I have formatted the columns to be military time. I have a function that subtracts the two times entered (start & finish) to give me total unload time. I am having an issue with the formula to average the unload time and get it to come out with the correct average. the formula I have entered is =average(r3:r13). It appears to be adding the cells, rather than giving an average.

 

[TulsaJeff]

I just tried your exact formula recreating exactly what you are doing and it is working fine. Anything else we need to know that might be causing This? You have a start time and an end time. this gives you your total load time which is being averaged...correct? Ya' Gotta Love It!X-)

 

[TulsaJeff]

Use this instead and see if you are coming up with the same average

=SUM(R3:R13)/COUNT(R3:R13) Ya' Gotta Love It!X-)

 

[JohnnyD]

I think I figured out what is causing the problem: It is being skewed by times that cross midnight. For example: load arrives 23:30 and is unloaded at 00:10. The formula to calculate the time is calculating correctly (showing 0:40), but is making the average jump because the system thinks it's 24:40????? How can I correct this error?

 

[JVFriederick]

Enter the load time as date+time and unload time the same way. Then average the variance.

((UnLoadDate+UnLoadTime)-(LoadDate+LoadTime))*24*60

will give the variance time in minutes.

 

[TulsaJeff]

I have your solution...Do an if statement in an adjacent column...

If finish time is less than start time then 24:00 - start time + Finish time...


Give me a few minutes and I will work up the formula for you... Ya' Gotta Love It!X-)

 

[TulsaJeff]

Johnny....did JVFriederick's solution work? It looked like the most accurate way to do what you wanted. I did not test it though so I am not putting my blessing on it yet...just inquiring. Ya' Gotta Love It!X-)

 

[JohnnyD]

Jeff,
JV's suggestion does work, but I have about 350 entries a day to key, so it is very time consuming. I took a page out of your last suggestion and developed a resolve that is less time consuming. in row W3, I entered 24:00, which pushes the date out 1 day. I then changed the IF statement to reflect: =IF(m3<k3,sum(m3+w3)-k3,sum(m3-k3)). This does exactly what I need. Thanks for the help.

 

[JVFriederick]

Another way using just time and no dates might be :
UT = UnLoadTime
LT = LoadedTime

=IF(UT/LT>1,UT-LT,1-LT+UT)

Multiply the result by 24*60 to convert to minutes.