Formula to average certain times

[Denise]

I have a large spreadsheet to track ER wait times. We use a new spreadsheet
for each month. Is there a way to calculate the average elapsed time for
patients that are admitted and patients that are dischared without having to
sort?

I have a column that is marked if the patient goes home with an "x". If
they are admitted there is a column for the room #. I have other columns
with the different wait times (wait time for triage, wait time to go to a
room, total elapsed time, etc.

Is there a formula to average the column with the elapsed time if the
discharge column contains an "x" and a formula to average the time if the
admission column has a room #.

Thanks for any help!!

 

[FSt1]

hi,
Assuming that you elapse time is in column A (which it probably isn't) and
assuming your markers( x and #) are in column b, the formula would look
somthing like this.....

=SUMIF(B3:B9,"x",A3:A8)/COUNTIF(B3:B9,"x")
=SUMIF(B3:B9,"#",A3:A9)/COUNTIF(B3:B10,"#")

You can adjust the ranges to fit your data.

Regards
FSt1

 

[Denise]

Thank you. I got the one to work for discharged patients since but I can't
get the one for admits to work. I am getting the error #DIV/0!. Each of the
room numbers will be different. What should I put in the formula so that it
will look at any number that is in that row?

Thanks again.

 

[FSt1]

hi,
If you are gettng the #DIV/0 error then the countif part of that formual is
not counting the # marker. room number should not be a factor. the formula
sums and counts based on the markers.( x or #). to say more, i would have to
see your data layout. I did assume the markers were in the same column. If
that is not the case, you will have to adjust the formula to fit your data. I
did test the formula and they both worked on my pc. except for the mark(x or
#) both formulas are identical.

FSt1

 

[Denise]

Here is an example of some rows:

HOME ADMIT WAIT TIME
x 00:50
5014-2 00:45
ICUA 00:37
x 00:25
x 7025 00:30

I am sure that there is just something that I am missing. I have tried and
tried the formula and it won't work for the admits. It will if the patient
goes home.

 

[FSt1]

hi again.
sorry to be so long getting back to you.i was a work and had to do some work.
and sorry for misunderstanding you.
For the formual that is not working try this.

=SUM(SUM(C4:C8)-SUMIF(B4:B8,"",C4:C8))/COUNTA(B4:B8)

where c is total time
b is x and c is room number

regards
FSt1