IF Statement Conversion from Excel to SharePoint 13

Question

I have 3 columns of where you can select any of the status types below, I would like to create a column that pivots based upon the status chosen. I have an excel formula that worked and I have seen other answers but I can not make the code work, I keep getting an error Can anybody help please?

The options in each drop column are
Pass
Fail
Not Assessed
In Progress-Documentation
In Progress-Awaiting Client

=IFS([8.1.1.1 Test Results]="Fail", "Fail", [8.1.1.2 Test Results]="Fail", "Fail", [8.1.1.3 Test Results]="Fail", "Fail", [8.1.1.1 Test Results]="", "", [8.1.1.2 Test Results]="", "", [8.1.1.3 Test Results]="", "", [8.1.1.1 Test Results]="In Progress-Awaiting Client", "In Progress-Awaiting Client", [8.1.1.2 Test Results]="In Progress-Awaiting Client", "In Progress-Awaiting Client", [8.1.1.3 Test Results]="In Progress-Awaiting Client", "In Progress-Awaiting Client",[8.1.1.1 Test Results]="In Progress-Documentation", "In Progress-Documentation", [8.1.1.2 Test Results]="In Progress-Documentation", "In Progress-Documentation", [8.1.1.3 Test Results]="In Progress-Documentation", "In Progress-Documentation", [8.1.1.1 Test Results]="Not Assessed", "Not Assessed", [8.1.1.2 Test Results]="Not Assessed", "Not Assessed", [8.1.1.3 Test Results]="Not Assessed", "Not Assessed", TRUE, "Pass")

Answer 1

Hi @Seth Kubli
First, I want to confirm some information with you. You have provided the formula:

=IFS([8.1.1.1 Test Results]="Fail", "Fail", [8.1.1.2 Test Results]="Fail", "Fail", [8.1.1.3 Test Results]="Fail", "Fail", [8.1.1.1 Test Results]="", "", [8.1.1.2 Test Results]="", "", [8.1.1.3 Test Results]="", "", [8.1.1.1 Test Results]="In Progress-Awaiting Client", "In Progress-Awaiting Client", [8.1.1.2 Test Results]="In Progress-Awaiting Client", "In Progress-Awaiting Client", [8.1.1.3 Test Results]="In Progress-Awaiting Client", "In Progress-Awaiting Client",[8.1.1.1 Test Results]="In Progress-Documentation", "In Progress-Documentation", [8.1.1.2 Test Results]="In Progress-Documentation", "In Progress-Documentation", [8.1.1.3 Test Results]="In Progress-Documentation", "In Progress-Documentation", [8.1.1.1 Test Results]="Not Assessed", "Not Assessed", [8.1.1.2 Test Results]="Not Assessed", "Not Assessed", [8.1.1.3 Test Results]="Not Assessed", "Not Assessed", TRUE, "Pass")

So, do you would like there is a priority in the formula like:

1. if one of the values in the three choice columns is "Fail", then the calculate value is "Fail"?
2. if [8.1.1.1 Test Results]="Fail", [8.1.1.2 Test Results]="In Progress-Awaiting Client", [8.1.1.3 Test Results]="In Progress-Documentation", the calculate value should be "Fail"?
3. Only if all the three choice columns value are "Pass", the calculate value would be "Pass"?

If so, you could use the following formula:

IF(OR([8.1.1.1 Test Results]="Fail",  [8.1.1.2 Test Results]="Fail", [8.1.1.3 Test Results]="Fail"), "Fail", IF(OR([8.1.1.1 Test Results]="", [8.1.1.2 Test Results]="", [8.1.1.3 Test Results]=""), "",IF(OR([8.1.1.1 Test Results]="In Progress-Awaiting Client", [8.1.1.2 Test Results]="In Progress-Awaiting Client",[8.1.1.3 Test Results]="In Progress-Awaiting Client"), "In Progress-Awaiting Client",IF(OR([8.1.1.1 Test Results]="In Progress-Documentation", [8.1.1.2 Test Results]="In Progress-Documentation", [8.1.1.3 Test Results]="In Progress-Documentation"), "In Progress-Documentation", IF(OR([8.1.1.1 Test Results]="Not Assessed", [8.1.1.2 Test Results]="Not Assessed", [8.1.1.3 Test Results]="Not Assessed"), "Not Assessed","Pass")))))  

As a result, you could see there is a clear prioritization relationship in this list:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.