Average time

  • Thread starter Thread starter bpc
  • Start date Start date
B

bpc

I have one column with time values in 24hr clock format (1742,812,1314,etc)
without colons. I can convert the times to a 12 hour clock and add the colon
but I would prefer not to. I need to calculate the average time of the
column. Suggestions? I have tried the average function which returns an
incorrect value.
 
try:

=(SUM(LEFT($E$1:$E$3,LEN($E$1:$E$3)-2)*60+RIGHT($E$1:$E$3,2))/
SUM(IF(LEN($E$1:$E$3)>0,1,0)))/60

this will give you time in hrs.000

then format is as time
 
=(SUMPRODUCT(INT(E1:E3/100))+INT(SUMPRODUCT(MOD(E1:E3,100))/60))/COUNT(E1:E3)*100
+INT(MOD(SUMPRODUCT(MOD(E1:E3,100)),60)/COUNT(E1:E3))
 
You can use the formula below, but it will error out on times less than
10:00. If you format the column with the times as text, and then make sure
you enter all times including preceding zeroes, then you will be good to go.
 
Another way

=--SUBSTITUTE(TEXT(AVERAGE(--(LEFT(A1:A3,LEN(A1:A3)-2)&":"&RIGHT(A1:A3,2))),"hh:mm"),":","")
 
forgot to metnion that it's an array formula

i.e. you have CTSL+SHIFT+ENTER it

instead of simply using ENTER
 
John

=LEFT(E2,LEN(E2)-2)*60+RIGHT(E2,2)

for 812 (which is 8:12 as I understand) it results with 492 minutes

right or wrong?
 
The problem you encounter isn't there. I was only concerned if they were only
entering the time as necessary, i.e: 1210 am would be entered as 10, and 1207
am would be entered as 7, and that is where it would error.
 
Does this array-entered** formula do what you want (adjust the cell ranges
to suit your situation)?

=AVERAGE(IF(A1:A100="","",--TEXT(A1:A100,"00\:00")))

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

Rick
 
Of course, the formula I posted returns the average time as a time value...
you probably want it in the same format your current times are in. To do
that, use this array-entered formula instead...

=TEXT(AVERAGE(IF(A1:A100="","",--TEXT(A1:A100,"00\:00"))),"hmm")

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

Rick
 
I know how you aspire to save keystrokes, and you should really return a
numeric value,

=--TEXT(AVERAGE(IF(A1:A100<>"",--TEXT(A1:A100,"00\:00"))),"hmm")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Yes, true, I do like to save keystrokes<g>, but that is not what happened
here. I did the modification to my first formula in my head while typing...
I simply forgot we were going for numerical results in the end (I think the
word "text" in the function name TEXT swayed me here). Thanks for catching
that.

Rick
 
You misunderstand, there was an AND in my words

Your formula followed by my revision

= TEXT(AVERAGE(IF(A1:A100="","",--TEXT(A1:A100,"00\:00"))),"hmm")
=--TEXT(AVERAGE(IF(A1:A100<>"",--TEXT(A1:A100,"00\:00"))),"hmm")
 
Ah yes, I see now (AVERAGE will ignore FALSE)... I had misunderstood your
"save keystrokes" comment and locked on the missing double unary.... I
didn't look inside the function for differences. Thanks again.

Rick
 
I have one column with time values in 24hr clock format (1742,812,1314,etc)
without colons. I can convert the times to a 12 hour clock and add the colon
but I would prefer not to. I need to calculate the average time of the
column. Suggestions? I have tried the average function which returns an
incorrect value.
Click to expand...


And how do you define the "average time"?`

Example: What is the expected output from an input of just the two
times 2345 and 0005?

Is it 1155 or is it 2355?

Maybe depends on what application this should be used for.

Lars-Åke
 
Back
Top