A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Time-of-day Average



 
 
Thread Tools Display Modes
  #1  
Old September 15th, 2003, 02:17 AM
GBL
external usenet poster
 
Posts: n/a
Default Time-of-day Average

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 a 17:55, 17:10, 18:00, 17:19, etc...

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

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

--
Sent by: GBL ΤΏΤ








  #2  
Old September 15th, 2003, 04:09 AM
Peo Sjoblom
external usenet poster
 
Posts: n/a
Default Time-of-day Average

No problems then, just use

=AVERAGE(Your_Range)

--

Regards,

Peo Sjoblom

"GBL" wrote in message
...
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 a 17:55, 17:10, 18:00, 17:19, etc...

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

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

--
Sent by: GBL ΤΏΤ










  #3  
Old September 15th, 2003, 01:03 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default Time-of-day Average

The hh:mm is simply a format for the result, as I am converting from time to
text, and putting it into the format I am assuming that you start with.

So it should now read

=TEXT(SUM(TIME(INT(H8:H51/100),E1:E100-INT(H8:H51/100)*100,0))/COUNTA(H8:H51
),"hhmm")

which should all be on one line

--

HTH

Bob Phillips

"GBL" wrote in message
...
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 a 17:55, 17:10, 18:00, 17:19, etc...

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

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

--
Sent by: GBL ΤΏΤ










 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.