Finding the average time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gadgets - 28 Jul 2006 21:55 GMT

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

Reply to this Message

Toppers - 29 Jul 2006 12:11 GMT

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

HTH

> Why doesn't the search function on these forums ever return anything to
> me.. sheesh.
[quoted text clipped - 16 lines]
> Thanks
> Brian

Reply to this Message

daddylonglegs - 29 Jul 2006 13:54 GMT

What formula do you have in W3:W4000?

Signature

daddylonglegs

Reply to this Message

Gadgets - 31 Jul 2006 17:27 GMT

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

Toppers Wrote:

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

Reply to this Message

Toppers - 31 Jul 2006 19:04 GMT

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
[quoted text clipped - 30 lines]
> >
> > HTH

Reply to this Message

daddylonglegs - 31 Jul 2006 21:06 GMT

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

Reply to this Message