SOLVED

MULTIPLE CONDITION FORMULAS - ASSISTANCE NEEDED

Copper Contributor

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: 

  • Asymptomatic: 10 days have passed since first positive viral diagnostic test.
  • Mild/Moderate: At least 10 days have passed since symptoms first appeared and at least 24 hours have passed since last fever without the use of fever-reducing medications and Symptoms (e.g., cough, shortness of breath) have improved.
  • Severe: At least 20 days have passed since symptoms first appeared and at least 24 hours have passed since last fever without the use of fever-reducing medications and Symptoms (e.g., cough, shortness of breath) have improved.

Here's a quick mockup of how my data is arranged:

 POSITIVE PCR TESTSTART OF SYMPTOMSCLASSIFICATIONDAYS WITH SYMPTOMSLAST DAY OF SYMPTOMSASYMPTOMATIC DAYSCURRENT DATEDURATION OF ISOLATIONPROBABLE RETURN DATE
#104/08/2020NAASYMPTOMATIC0NA1620/08/202016 
#210/08/202010/08/2020MILD717/08/2020320/08/202010 
#308/08/202008/08/2020MODERATE513/08/2020720/08/202012 
#407/08/202005/08/2020MODERATE15NANA20/08/202015 
#502/08/202002/08/2020SEVERE1012/08/2020820/08/202018 

 

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:

  • If the cell from Classification Column says: "ASYMPTOMATIC" then the "PROBABLE RETURN DATE" should be 10 Days plus Positive PCR Test Date.
  • After that, the formula should check if the value of the cell inside ASYMPTOMATIC DAYS Days is greater than 3 and, if it fails, it should say NA but, if it passes, then it should do the following:
    • If the cell from Classification Column says: "MILD" or "MODERATE" then the "PROBABLE RETURN DATE" should be 10 Days plus the date from START OF SYMPTOMS OR 3 days after the last days of symptoms if the value from the DAYS WITH SYMPTOMS is greater than 10.
    • If the cell from Classification Column says: "SEVERE" then the "PROBABLE RETURN DATE" should be 20 Days plus the date from START OF SYMPTOMS OR 3 days after the last days of symptoms if the value from the DAYS WITH SYMPTOMS is greater than 20.

I really hope I was able to explain myself properly. Any advice is welcomed. 

2 Replies
best response confirmed by BattleBorn (Copper Contributor)
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)

 

 

 

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.

1 best response

Accepted Solutions
best response confirmed by BattleBorn (Copper Contributor)
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)

 

 

 

View solution in original post