Complex IF function application

Copper Contributor

Hello, 

 

My team uses SharePoint to track multi-step projects from scheduling to completion. We are building a resource loading feature into this tracker. To do this, we have assigned Phase designations to specific steps in the process (Phase 0, Phase 1, etc.) as a way to determine resources needed for that project at that phase of the project (e.g "Project A is in Phase 3, so there are 28hrs left on it").

 

We have eight steps we use to determine what phase a project is in (Site Prep, Protocol Status, DIRA Status, SOP Status, PC Setup, Back Up, Qual Report, Eq Release).

 

Ideally, we would be able to set up a calculated column to return "Phase 0, Phase 1, Phase 2, etc" based on the specific status of each of those steps (each are represented as a different column in our SharePoint). The issue I am coming up on is that for different projects, each Phase may have more than one combination of statuses for those eight categories. For instance, Phase 1 may see the PC Setup column as "Not Started" or "Not Applicable" but still be in Phase 1. I'm not sure if this is a problem that can be solved with a specific formula, or if I need to look at other solutions. 

2 Replies

@paccagnd If you know the right combination of conditions, you should be able to achieve it using SharePoint calculated formula.

 

Check my answer here to know more about nested conditions & number of characters limitation in calculated formula and workaround: What is the maximum syntax formulas that I can use in calculated column on my SharePoint list? 


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

This is the current formula we are using: =IF(AND([Site Prep Status]="Not Started",[Protocol Pre-Approval Status]="Not Started",[DIRA Status]="Not Started",[SOP Status]="Not Applicable",[LCS Protocol Status]="Not Started",[LDM Protocol Status]="Not Started",[Qual Report Status]="Not Started",[Eq Release Date]=""),"Phase 0",IF(AND([Site Prep Status]="Completed",[Protocol Pre-Approval Status]="Approved",[DIRA Status]="Not Started",[SOP Status]="Not Started",[LCS Protocol Status]="Not Started",[LDM Protocol Status]="Not Started",[Qual Report Status]="Not Started",[Eq Release Date]=""),"Phase 1",IF(AND([Site Prep Status]="Completed",[Protocol Pre-Approval Status]="Approved",[DIRA Status]="Completed",[SOP Status]="In Review",[LCS Protocol Status]="Completed",[LDM Protocol Status]="Completed",[Qual Report Status]="Not Started",[Eq Release Date]=""),"Phase 2",IF(AND([Site Prep Status]="Completed",[Protocol Pre-Approval Status]="Approved",[DIRA Status]="Approved",[SOP Status]="Approved",[LCS Protocol Status]="Completed",[LDM Protocol Status]="Completed",[Qual Report Status]="Not Started",[Eq Release Date]=""),"Phase 3",IF(AND([Site Prep Status]="Completed",[Protocol Pre-Approval Status]="Approved",[DIRA Status]="Approved",[SOP Status]="Approved",[LCS Protocol Status]="Completed",[LDM Protocol Status]="Completed",[Qual Report Status]="Approved",[Eq Release Date]=""),"Phase 4",IF(AND([Site Prep Status]="Completed",[Protocol Pre-Approval Status]="Approved",[DIRA Status]="Approved",[SOP Status]="Approved",[LCS Protocol Status]="Completed",[LDM Protocol Status]="Completed",[Qual Report Status]="Approved",[Eq Release Date]<>""),"Phase 5",""))))))

The issue is that for each Phase, there may be more than one combination of conditions and I'm not sure how to adjust this formula to add that complexity.