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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|