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.