Finding the average time
 

Why doesn't the search function on these forums ever return anything to
me.. sheesh.

Anyway, I have a column of times, in this column there are blank cells
(will have a formula embedded in the cell but no results showing). I
need to find the average time. It works until I hit a bank cell, then
the average goes wacky!

The cell formats are h:mm;; I have also tried 0:00;;

I am using =average(W3:W4000) At the moment I have a value of -222.54
this value should never be a negative number. The values in the cells
are all positive time frame (ie. 0:15 (15 minutes), 3:15 (3 hrs and 15
mins). For that matter I can't seem to get a total time from that
column either, it too ends up wierd. The format of the cell (although I
have tried many) the results end up in is hh:mm (at the moment I have
tried h:mm etc.).

Thanks
Brian


--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=566175

Finding the average time
 

Format your cells as [hh:mm]. I assume time means a time interval as opposed
to a clock time: average ignores blank cells.

HTH

"Gadgets" wrote:


Why doesn't the search function on these forums ever return anything to
me.. sheesh.

Anyway, I have a column of times, in this column there are blank cells
(will have a formula embedded in the cell but no results showing). I
need to find the average time. It works until I hit a bank cell, then
the average goes wacky!

The cell formats are h:mm;; I have also tried 0:00;;

I am using =average(W3:W4000) At the moment I have a value of -222.54
this value should never be a negative number. The values in the cells
are all positive time frame (ie. 0:15 (15 minutes), 3:15 (3 hrs and 15
mins). For that matter I can't seem to get a total time from that
column either, it too ends up wierd. The format of the cell (although I
have tried many) the results end up in is hh:mm (at the moment I have
tried h:mm etc.).

Thanks
Brian


--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=566175

Finding the average time
 

What formula do you have in W3:W4000?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566175

Finding the average time
 

The time is total number of hours a person stayed in the hospital. And
I'd like the average of these times so we know "on average" how long a
person stays in the hospital for this particular illness.

Currently I have the cell format set to 0:00;; because if I use hh:mm
it's not giving me the correct answers.

Example: A person stays in the hospital over a month, in this case
33.76 days. The amount of hours is 810.30 (when the cell is using
format 0:00) if I format it to hh:mm I get a value of 07:12?

Now, to get either of those values I am subtracting one cell from
another, and these cells are both formatted the same *dd/mm/yyyy h:mm*

My current total using the average function in a cell formatted 0:00 is

-5342.29
If I average just say 2 or 3 cells it works, but throw in a blank cell
and that's when the totals mean nothing. Average ignores BLANK cells,
but does it ignore cells with formulas in them?

To answer daddylonglegs my formula in w3:w4000 is =AVERAGE(V3:V4000) is
that what you wanted? or what formula is embedded in those cells, that
would be =(S3-A3)*24

Thanks for the help here,
Brian


Toppers Wrote:
Format your cells as [hh:mm]. I assume time means a time interval as
opposed
to a clock time: average ignores blank cells.

HTH


--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=566175

Finding the average time
 

First, set format of cell that contains elapsed time to [h]:mm (as per my
original posting) so for dates below you get elapsed time of 2163 hours

Entered Hospital Left Hospital Elapsed time (hours)
02/05/2006 14:00 31/07/2006 17:00 2163:00

You might want to use this formula in the cells that calculate elapsed time:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",B1-A1) so if either date is missing,
elapsed time is set to blank assuming times in columns A & B.

If elapsed time is in column C and there are 1000 rows of data (including
blanks) then

=AVERAGE(C1:C1000) formatted as [h]:mm will get average.

If you still have problems post w/book to toppers<atjohntopley.fsnet.co.uk

HTH

"Gadgets" wrote:


The time is total number of hours a person stayed in the hospital. And
I'd like the average of these times so we know "on average" how long a
person stays in the hospital for this particular illness.

Currently I have the cell format set to 0:00;; because if I use hh:mm
it's not giving me the correct answers.

Example: A person stays in the hospital over a month, in this case
33.76 days. The amount of hours is 810.30 (when the cell is using
format 0:00) if I format it to hh:mm I get a value of 07:12?

Now, to get either of those values I am subtracting one cell from
another, and these cells are both formatted the same *dd/mm/yyyy h:mm*

My current total using the average function in a cell formatted 0:00 is

-5342.29
If I average just say 2 or 3 cells it works, but throw in a blank cell
and that's when the totals mean nothing. Average ignores BLANK cells,
but does it ignore cells with formulas in them?

To answer daddylonglegs my formula in w3:w4000 is =AVERAGE(V3:V4000) is
that what you wanted? or what formula is embedded in those cells, that
would be =(S3-A3)*24

Thanks for the help here,
Brian


Toppers Wrote:
Format your cells as [hh:mm]. I assume time means a time interval as
opposed
to a clock time: average ignores blank cells.

HTH


--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=566175

Finding the average time
 

If you use this formula to calculate the elapsed time

=(S3-A3)*24

then when S3 is blank you may get a large negative number (what does A3
contain, todays' date?)

You don't see the large negative value because the format 0.00;; will
hide negative values but the value is still there (formats don't change
values they just change the display of them) and will be included in the
average, hence your negative value. Average only ignores "real" blanks,
not those manufactured by formatting

I'm sure Toppers' approach will work for you, or just change your
formula to

=IF(S3,(S3-A3)*24,"")

I'm assuming that A3 won't be blank and S3 contain a date.......


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566175