Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to average certain times
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!! |
#2
|
|||
|
|||
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" wrote: 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!! |
#3
|
|||
|
|||
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" wrote: 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" wrote: 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!! |
#4
|
|||
|
|||
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" wrote: 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" wrote: 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" wrote: 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!! |
#5
|
|||
|
|||
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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!! |
#6
|
|||
|
|||
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 "Denise" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding typing similar formula 600 times | Excel Worksheet Functions | |||
Average Formula with Criteria | Excel Discussion (Misc queries) | |||
Formula for averaging times | Excel Discussion (Misc queries) | |||
#N/A In Max min Average Formula | Excel Worksheet Functions | |||
Average Formula | Excel Worksheet Functions |