Average time

[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.

 

[Jarek Kujawa]

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

 

[Bob Phillips]

=(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))

 

[John C]

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.

 

[Bob Phillips]

Another way

=--SUBSTITUTE(TEXT(AVERAGE(--(LEFT(A1:A3,LEN(A1:A3)-2)&":"&RIGHT(A1:A3,2))),"hh:mm"),":","")

 

[Jarek Kujawa]

forgot to metnion that it's an array formula

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

instead of simply using ENTER

 

[Jarek Kujawa]

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?

 

[Bob Phillips]

This last one is an array formula too.

 

[John C]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob Phillips]

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)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob Phillips]

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")

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Lars-Åke Aspelin]

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.

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