|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Logic formula to only count patient once, even though they are listed multiple times
I am trying to find a formula that would count an item once, even though, it possibly could appear in the data multiple times. These are babies who stayed in our NICU overnight. We populate this spreadsheet daily, with who was in there at midnight. So, if a baby stays in there for 10 days, they will be on this spreadsheet 10 times. The data element that I'm trying to get at is the # of admits (column Q). I did a simple formula that said if the Date of service (column J) was the same as the babies admit date (column C), to put a 1. There are times though that the baby will not go straight to the NICU. In that case, I am not ever counting that baby as an admit. I need a way to identify those patients that have not been counted yet in column Q, but only count them on their first day in the NICU. |
#2
|
||||
|
||||
Hi and welcome
Quote:
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
If the baby's admit date is less than their first occurance date, in column J, then they were possibly in with the mother when they were first born, not needing to be in the NICU. Does this make sense? An example of this is Pt Nbr (column B) is 2452590. As you can see, their first day in the NICU was 1/11/13, but baby was born on 1/9/13. So, in this instance, I would want to count the baby as an admit on the 1/11/13 day only, even though the baby stayed in the NICU for 48 days.
|
#4
|
||||
|
||||
Carol, is this just a matter of counting how many different patient numbers there are in a given range of dates? Or do you need to count a patient twice if it was admitted, then moved elsewhere, then admitted again (because of a relapse, say)?
|
#5
|
|||
|
|||
Each person can only be counted once, regardless if they relapsed or not.
|
#6
|
||||
|
||||
So forget looking at columns C, J and Q; all your formula needs is to look at column B and report how many different patient numbers are there, no matter how many times they appear. Will that work, or am I missing something? In the worksheet you posted, I see 244 different patient numbers. If you have a formula that tells you that for any range of rows, will that satisfy your requirement?
|
#7
|
|||
|
|||
I thought I had posted a reply yesterday, but I don't see it here, so I will repost, just in case.
No, I need more than just a simple formula that counts up the # of different patient account #'s. The # of admits is one of the main key indicators that hospitals have to report, so it is important that we count the baby in the correct month, and only once, for the whole stay. Here are a few scenarios that will hopefully help explain better. Scenario 1: Baby born 12/29/13 - went into NICU right away Admit will be counted for 12/29/13 Baby still in NICU through 1/20/14. In January I would not count an admit on the baby, since they STARTED in the NICU in December. Scenario 2: Baby admitted 12/29/13 - to a regular room Baby became ill and had to go into NICU on 1/2/14 Baby in NICU through 1/10/14. I would need to count this baby as an admit on 1/2/14, since that is when they came into the NICU. Normal (non-sick) babies do not get counted, so since baby was in a normal room with the mother, it wasn't counted in December. We would only count the baby once it got admitted into the NICU. Does this help? |
#8
|
||||
|
||||
I think it helps, but let me rephrase to make sure. No, first let me ask something I should have asked at first: Would I be right in the following?
|
#9
|
|||
|
|||
You are definitely on the right track. I wanted to clarify something on the 3rd scenario - if they were admitted 2 weeks later, they would be assigned a different account #.
SCN & NICU are synonymous for us. Also, on your first scenario, since we don't have the 2012 data in this spreadsheet, we really wouldn't know if the baby was in the SCN/NICU for the December days, without looking. I'm thinking for those patients who cross over into a new year, I would have to just manually check those to make sure that I'm counting it correctly, or add another tab with 2012 data. Either way, for your purposes, we will assume that we already counted the admit in December. Thanks for the great questions/clarifications! |
#10
|
||||
|
||||
On that first example, I think you can be sure the baby spent some days in the SCN in 2012 because the admit date in col C is 2012-12-24. (Christmas Eve, he muses. I entered the hospital Christmas Eve of 2010 and didn't get out 'til Mother's Day; swine flu.) But it doesn't matter; the relevant factor is that the admit date isn't 2013, so the patient isn't to be included in this count.
Now, about the 3rd example: I get it, the policy is to assign a new patient number in such a case. (I thought you might say that, in fact.) But like all old and grizzled programmers with decades of experience, I am untrusting. However impossible it may seem to you that a patient be admitted a second time with the old patient number, I nevertheless must ask: If the impossible should happen, how should it be counted? Three possible answers: If the patient is readmitted with the same patient number...
I haven't asked this yet, but what's your final output? Are you looking for just a number (244 admissions)? Do you need a list of 244 rows, one for each patient showing the admit date and maybe the discharge date? What? One last consideration: How shall we do this? If you have to do this only once a year, then I'm guessing it isn't worth the trouble to write a VBA program for it; I can just show you how to massage the date with a sort and some functions to come up with the result. If I've misunderstood and you need to do this a lot more often—maybe you want to do the same thing to 43 other worksheets, once this one is done—then a VBA program is probably better. |
#11
|
|||
|
|||
I can not always go by the patients' admit date, due to the fact that if the baby isn't a sick baby, we don't get to count that admission. So, it is quite possible that the baby could have worsened a few days after being born, and then had to be moved to the SCN/NICU. Like I said, those are my exceptions, and I will just know to look for those type of scenarios, and correct my data accordingly.
Since we should NEVER admit a patient back to the hospital, using the same account #, I would prefer that those are flagged, so that I can bring it to our admitting department's attention - this would have to be corrected on their end. SO...we can just highlight it, and then I will follow up. I do not foresee that this would be an issue. We calculate days, admits, etc. on a daily basis, for all of our patients. For the SCN, that department was keeping manual stats that had to be reported once a week, which included # of admits, days, and now our administrators want it compared to last year as well. So, I have dumped in all of 2013, and will be dumping in the 2014 data on a separate tab. I would need to see admits and days (days would be just a count, by month, for every day they are housed in the SCN/NICU). Our final output wouldn't include patient account #'s - just totals by month. |
#12
|
||||
|
||||
Wait, I'm missing something. Isn't the patient number in col B the baby's ID? I mean, the patient is the baby, not the mother, right? And isn't col C the date the patient entered the NICU? Apparently one or both of those assumptions are wrong, if you can't always go by the admit date.
About desired output: What I'm hearing is that it worked alright to do this manually on a daily and weekly basis, but a) to do the comparison for a whole year's worth you need Excel to do a lot of the work for you, and b) since you're doing that you may as well make it work for you on the weekly stats too. So we're talking about two kinds of output: A comparison of 2013 with 2012, and (if it can be done conveniently) a better way of doing the weekly summary. And for that matter you have to collect daily numbers too. But first things first: The whole-year comparison is the one that would cost you the most effort. Now, I'm hearing two different things about that:
|
#13
|
|||
|
|||
Sorry for the delay in getting back to you - things have been crazy busy at work.
The patient # IS in column B, and all of these patients are the babies #, not the mom's #. Column C is NOT NECESSARILY the day they entered the NICU - that would be column J the first time that account # appeared. It is possible that baby was admitted into the mom's room, on the day of birth, but 2 days later took a turn for the worse. In that case, the admit date would be, for example, 2/22/14, but the first day they came to the NICU was 2/24/14, which would be what shows up in column J. Now, if they are still in the NICU on 2/25/14, column C would still say 2/22/14, and column J would now say 2/25/14. Basically, column J just shows every date of service that they spent in the NICU. As far as the desired output: We are going to keep up with this spreadsheet on a daily basis - loading the new babies that entered the NICU on the previous day. Our CEO would like to see, probably by month, the # of babies for each year (2013 & 2014), with a variance column. It would also be nice to see what the average length of stay would be. (We would have to count the # of times the babies account # appeared). If a baby is in the NICU for more than a month, and if we are reporting on January, we would only count up January's days in the January comparison, and then the February dates of service in the February comparison. Does that makes sense? The other thing that we have to report is how many admits we had in a particular month. If the patient was admitted to the NICU in January, but we are reporting on February, we would NOT count the admit in February, since we are only allowed to count an admit once for their hospital stay. I had originally thought I could say if(C=J,1,""), but since there are times that baby stays in mom's room the first day, it would never count that baby as an admit to the NICU. I'm not sure how we can capture those types of babies. Did I clear things up, or do you still have questions? Thank you for your help! |
#14
|
||||
|
||||
IMO all this discussion of what the data are and how they're derived seems to be focussing on irrelevancies!
Assuming the data to be counted are in column B, you could use the following formula to get a 'unique items' count (i.e. each item is counted only once) in any other column: =SUMPRODUCT((B$3:OFFSET(B$3,COUNTA(B:B)-1,)<>0)/COUNTIF(B$3:OFFSET(B$3,COUNTA(B:B)-1,),B$3:OFFSET(B$3,COUNTA(B:B)-1,)))-1
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
I entered that formula into my spreadsheet, and it brought back 0 in every cell.
|
Tags |
count logic, if then |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word Count Formula | clau_dos | Word VBA | 1 | 08-27-2013 02:05 PM |
Selecting the same word multiple times at one go | No.1 | Word | 3 | 08-08-2013 06:29 PM |
make a form that can be used multiple times at once | Dsp581 | Excel | 19 | 04-05-2013 07:25 AM |
Count If Formula | beb1227 | Excel | 10 | 06-19-2011 11:19 AM |
Outlook Sending Same Email Multiple Times | mudpuppy | Outlook | 1 | 08-03-2010 06:39 PM |