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.