mardi 6 novembre 2018

Combine two nested IF statements with multiple criteria

I have two columns of data in "Meds" sheet...

  MedContinuing     AgeAtMedStop
      Yes             "Blank"
      Yes              72.22
      No              "Blank"
      No               72.57
    "Blank"            73.85

I am writing a formula in a separate sheet to return 1 or 0 based on the following:

  1. If MedContinuing is "Blank", do nothing
  2. If MedContinuing is "No" and AgeAtMedStop is blank, do nothing
  3. If MedContinuing is "Yes" and AgeAtMedStop is "Blank", return 1. If AgeAtMedStop is a number, return 0.
  4. If MedContinuing is "No" and AgeAtMedStop is a number, return 1. Otherwise, return nothing.

I was able to write two separate functions (see below) for when MedContinuing is "Yes" or when it is "No", but I need to combine both into one formula.

When it's Yes...

=IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="","",
IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="No","",
IF(AND(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="Yes",INDEX(Meds!2:2,MATCH("AgeAtMedStop",Meds!$1:$1,0))=""),1,0)))

When it's No...

=IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="","",
IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="Yes","",
IF(AND(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="No",INDEX(Meds!2:2,MATCH("AgeAtMedStop",Meds!$1:$1,0))=""),"",
IF(AND(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="No",INDEX(Meds!2:2,MATCH("AgeAtMedStop",Meds!$1:$1,0))>0),1,0))))

Aucun commentaire:

Enregistrer un commentaire