Formula to average certain times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!
 
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
 
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.
 
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
 
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.
 
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
 
Back
Top