Finding the average time

[Gadgets]

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

Anyway, I have a column of times, in this column there are blank cell
(will have a formula embedded in the cell but no results showing).
need to find the average time. It works until I hit a bank cell, the
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.5
this value should never be a negative number. The values in the cell
are all positive time frame (ie. 0:15 (15 minutes), 3:15 (3 hrs and 1
mins). For that matter I can't seem to get a total time from tha
column either, it too ends up wierd. The format of the cell (although
have tried many) the results end up in is hh:mm (at the moment I hav
tried h:mm etc.).

Thanks
Bria

 

[Guest]

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

HTH

 

[daddylonglegs]

What formula do you have in W3:W4000?

 

[Gadgets]

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

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

Example: A person stays in the hospital over a month, in this cas
33.76 days. The amount of hours is 810.30 (when the cell is usin
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 fro
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 i

-5342.2
If I average just say 2 or 3 cells it works, but throw in a blank cel
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) i
that what you wanted? or what formula is embedded in those cells, tha
would be =(S3-A3)*24

Thanks for the help here,
Brian

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

HT

 

[Guest]

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

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

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

HTH

 

[daddylonglegs]

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 A
contain, todays' date?)

You don't see the large negative value because the format 0.00;; wil
hide negative values but the value is still there (formats don't chang
values they just change the display of them) and will be included in th
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 you
formula to

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

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