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.

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

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")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.