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.