Aug 04 2020 09:56 AM
I'm trying to implement the CDC's criteria to discharge a patient frome home isolation into a spreadsheet to automate the process as much as it can be. The criteria they recommend to use is as follows:
Here's a quick mockup of how my data is arranged:
POSITIVE PCR TEST | START OF SYMPTOMS | CLASSIFICATION | DAYS WITH SYMPTOMS | LAST DAY OF SYMPTOMS | ASYMPTOMATIC DAYS | CURRENT DATE | DURATION OF ISOLATION | PROBABLE RETURN DATE | |
#1 | 04/08/2020 | NA | ASYMPTOMATIC | 0 | NA | 16 | 20/08/2020 | 16 | |
#2 | 10/08/2020 | 10/08/2020 | MILD | 7 | 17/08/2020 | 3 | 20/08/2020 | 10 | |
#3 | 08/08/2020 | 08/08/2020 | MODERATE | 5 | 13/08/2020 | 7 | 20/08/2020 | 12 | |
#4 | 07/08/2020 | 05/08/2020 | MODERATE | 15 | NA | NA | 20/08/2020 | 15 | |
#5 | 02/08/2020 | 02/08/2020 | SEVERE | 10 | 12/08/2020 | 8 | 20/08/2020 | 18 |
So what I'm trying to automatically get is the PROBABLE RETURN DATE column but the formula should check for all the criteria (plus an extra one) kind of like this:
I really hope I was able to explain myself properly. Any advice is welcomed.
Aug 04 2020 10:24 AM - edited Aug 04 2020 10:28 AM
Solution@BattleBorn I think the following will work. note, I'm assuming your table starts in the upper left corner cell of A1 and that Asymptomatic will have "na" or the such (i.e. cause the error to return the B2+10) and I use +3 for days after last symptom even though the guidelines say 24hrs not 72hrs and finally if last day of symptoms is NA (or other non-date) I used TODAY() + 3 as the estimate because I didn't know what you wanted.
=IFERROR(MAX(C2+10*(1+(D2="SEVERE")),IFERROR(F2+3,TODAY()+3)),B2+10)
Aug 04 2020 11:14 AM
I added those extra 3 days as an additional precaution measure after reviewing the WHO's guidelines. So it's sort of a combination of both.
I think this might be working. Thanks so much, @mtarler.
Aug 04 2020 10:24 AM - edited Aug 04 2020 10:28 AM
Solution@BattleBorn I think the following will work. note, I'm assuming your table starts in the upper left corner cell of A1 and that Asymptomatic will have "na" or the such (i.e. cause the error to return the B2+10) and I use +3 for days after last symptom even though the guidelines say 24hrs not 72hrs and finally if last day of symptoms is NA (or other non-date) I used TODAY() + 3 as the estimate because I didn't know what you wanted.
=IFERROR(MAX(C2+10*(1+(D2="SEVERE")),IFERROR(F2+3,TODAY()+3)),B2+10)