1

I am attempting to calculate the outcome a status field based on the outcome of other columns. They are all Test Fields.

For Example:

"Overall Approval Status" column is to be based on Accepted/Rejected outcome in 8 Columns "Approver Status 1, "Approver Status 2", "Approver Status 3", "Approver Status 4", "Approver Status 5","Approver Status 6","Approver Status 7","Approver Status 8".

If any of the Approver Status fields has outcome of "Rejected" then the Overall Approver Status Column will return "Rejected". If all Approver Status columns have an outcome of "Approved" then the Overall Approver Status Column will return"Approved"

how would I write this formula for the " Overall Approver Status" Column?

I am really stumped and could you some expert assistance to solve this issue.

Improve this question

2 Answers 2

Reset to default
0

It's preferred to use a workflow to achieve nested if easily using SharePoint Designer as the following:

enter image description here

Otherwise, you will need to write multiple lines in the Calculated column to achieve the nested If!

To use nested-If in a calculated column, check

Improve this answer
0

This returns "Approved" if all eight are "Approved", otherwise "Rejected".

=IF(AND([Approver Status 1]="Approved",[Approver Status 2]="Approved",[Approver Status 3]="Approved",[Approver Status 4]="Approved",[Approver Status 5]="Approved",[Approver Status 6]="Approved",[Approver Status 7]="Approved",[Approver Status 8]="Approved"),"Approved","Rejected")

This is a bit better as it returns "Rejected" if any are rejected ("OR") or "Approved" if all are approved ("AND"), otherwise returns "Pending".

=IF(AND([Approver Status 1]="Approved",[Approver Status 2]="Approved",[Approver Status 3]="Approved",[Approver Status 4]="Approved",[Approver Status 5]="Approved",[Approver Status 6]="Approved",[Approver Status 7]="Approved",[Approver Status 8]="Approved"),"Approved",IF(OR([Approver Status 1]="Rejected",[Approver Status 2]="Rejected",[Approver Status 3]="Rejected",[Approver Status 4]="Rejected",[Approver Status 5]="Rejected",[Approver Status 6]="Rejected",[Approver Status 7]="Rejected",[Approver Status 8]="Rejected"),"Rejected","Pending")

Little better formatted version:

=IF( AND([Approver Status 1]="Approved", [Approver Status 2]="Approved", [Approver Status 3]="Approved", [Approver Status 4]="Approved", [Approver Status 5]="Approved", [Approver Status 6]="Approved", [Approver Status 7]="Approved", [Approver Status 8]="Approved"),"Approved", IF(OR([Approver Status 1]="Rejected", [Approver Status 2]="Rejected", [Approver Status 3]="Rejected", [Approver Status 4]="Rejected", [Approver Status 5]="Rejected", [Approver Status 6]="Rejected", [Approver Status 7]="Rejected", [Approver Status 8]="Rejected"),"Rejected", "Pending")
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.