Time-of-day Average

[GBL]

Hi:
I have numerous Excel 2000 cells (some of which are blank) that have
time-of-day entries (military-time formatted as text).
I need a formula for the average time-of-day from these entries. Any
ideas??
Thanks in Advance!!

 

[Bob Phillips]

Try this as a starter
=TEXT(SUM(TIME(INT(E1:E100/100),E1:E100-INT(E1:E100/100)*100,0))/COUNTA(E1:E
100),"hhmm")
it's an array formula so enter with Ctrrl-Shift-Enter not just Enter

Change the range to suit

 

[Peo Sjoblom]

What is military time formated as text?
If you mean that the cells are formatted as text and then time
is entered as 0900 and 2100 you could try something like


=AVERAGE(IF(ISNUMBER((LEFT(A1:A10,2)&":"&RIGHT(A1:A10,2))*1),(LEFT(A1:A10,2)
&":"&RIGHT(A1:A10,2))*1))

entered with ctrl + shift & enter

If 09:00 is entered as 900 the above won't work

I would probably use a help column

and someting like

=IF(A1="","",IF(LEN(A1)=3,(LEFT(A1)&":"&RIGHT(A1,2))*1,(LEFT(A1:A1,2)&":"&RI
GHT(A1:A1,2))*1))

copy down as long as needed then do the average

=AVERAGE(A1:A10)

 

[Daniel.M]

Hi,

I don't know what military time formatted as text exactly means.

But if it's somewhere around '0900, you could try the following ARRAY
formula (Ctrl-Shift-Enter):

=AVERAGE(IF(A1:A20<>"",A1:A20+0))/2400

And format your result as time (h:mm or compatible format).

Regards,

Daniel M.

 

[GBL]

Hi Bob:
Thanks for your reply Bob.
In the formula you've sent, I've tried substitution to my cell designations
and I'm getting an error. Trying to understand the logic. Hmmm - maybe if I
try braking it down into sections. Will keep trying.
Best Regards,
Bruce

 

[Bob Phillips]

Did you spot that my formula spiller over 2 lines, be sure to put it all on
one line.

What are your cell designations?

If you want to send me the workbook, I will put it in for you.

--

HTH

Bob Phillips

Hi Bob:
Thanks for your reply Bob.
In the formula you've sent, I've tried substitution to my cell designations
and I'm getting an error. Trying to understand the logic. Hmmm - maybe if I
try braking it down into sections. Will keep trying.
Best Regards,
Bruce

 

[GBL]

Hi:
Cell designations are H8:H51.
I used your entire formula. But didn't understand where you got the "hhmm"
designation.
My military-time cell examples are: 17:55, 17:10, 18:00, 17:19, etc...

Did you spot that my formula spiller over 2 lines, be sure to put it all on
one line.

What are your cell designations?

If you want to send me the workbook, I will put it in for you.

--

HTH

Bob Phillips

Hi Bob:
Thanks for your reply Bob.
In the formula you've sent, I've tried substitution to my cell designations
and I'm getting an error. Trying to understand the logic. Hmmm - maybe

if

 

[Peo Sjoblom]

No problems then, just use

=AVERAGE(Your_Range)