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

--
__________________________________
HTH

Bob

"bpc" wrote in message
...
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.

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.
--
John C


"Jarek Kujawa" wrote:

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

Another way

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

--
__________________________________
HTH

Bob

"bpc" wrote in message
...
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.

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?

This last one is an array formula too.

--
__________________________________
HTH

Bob

"Bob Phillips" wrote in message
...
Another way

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

--
__________________________________
HTH

Bob

"bpc" wrote in message
...
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.

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.
--
John C


"Jarek Kujawa" wrote:

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?

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


"bpc" wrote in message
...
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.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"bpc" wrote in message
...
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.

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)" wrote in
message ...
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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"bpc" wrote in message
...
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.

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" wrote in message
...
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)" wrote in
message ...
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


"Rick Rothstein (MVP - VB)" wrote
in message ...
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


"bpc" wrote in message
...
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.

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

--
__________________________________
HTH

Bob

"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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)" wrote
in message ...
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


"Rick Rothstein (MVP - VB)" wrote
in message ...
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


"bpc" wrote in message
...
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.

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


"Bob Phillips" wrote in message
...
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")

--
__________________________________
HTH

Bob

"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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)" wrote
in message ...
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


"Rick Rothstein (MVP - VB)" wrote
in message ...
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


"bpc" wrote in message
...
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.

On Mon, 14 Jul 2008 06:21:02 -0700, bpc
wrote:

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