Revise Formula

jgneely72151

Far below is a screemsjpt of how I track whether or not students have met compliance.

Currently, they all start out as non-compliant when we add them to the sheet.

Once the last 2 columns are checked, the overall status outputs "Compliant",

Now I have an extra condition to add (Student health colume) and I'm not quite sure how to update the formula.

Here is the current formula (The 2nd IF statement is contingent on the first):

=IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), "Compliant", "Non-Compliant"))

I would like the results to be that if the last 2 columes (ACEMAPP and Advantage) are checked, the Overall Compliance Status output should be "Partially Compliant". If all 3 columes are checked, then the overall status output should be "Compliant".

Any help is greatly appreciated!

Mark.poole

@jgneely72151

=IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed", [Compliant w/Student Health?] = 1), "Compliant", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), “Compliant”, "Non-Compliant")))

The parts I changed are bolded

Mark.poole

@jgneely72151

I noticed a typo in my original post. I corrected it below.

=IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed", [Compliant w/Student Health?] = 1), "Partially Compliant", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), “Compliant”, "Non-Compliant")))

jgneely72151

I'm sorry; I'm getting unparseable.

jgneely72151

Here are my results and the last one should show compliant when all fields are checked or has background completed. I also don't have the first If statement that marks the status as cancelled. It works by itself but not with the If statements. Not sure if something is missing. My notes are in the last column on how each worked when I select a flag or check boxes.

Mark.poole

@jgneely72151

=IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed", [Compliant w/Student Health?]@row = 1), "Compliant", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), "Partially Compliant", "Non-Compliant")))

I am not sure how you got the blue check marks. That could be part of the issue. I am only able to get green check marks. so you may have to fix that in the formula. how ever. This is the formula with a screen shot of it working.