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

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
>
>

What formula do you have in W3:W4000?

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


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

HTH

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<at>johntopley.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
>
>

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.......