If statements with multiple conditions



Hoping someone can help with a formula I am struggling to get to work for a specific scenario. I have a "phase column" in my project plan which looks at various parent rows for my project phases, i.e Discovery, Design, Build, Implement, Benefits realisation. The formula assesses the status of these phases, so I can indicate at a high level what phase the project is on. Below is the statement I use, which works perfectly when each phase is done in chronological order. 

=IF(Status2 = "In Progress", "Discovery", IF(Status15 = "In Progress", "Design", IF(Status116 = "In Progress", "Build", IF(Status159 = "In Progress", "Implement", IF(Status194 = "In Progress", "Benefits Realisation", IF(Status93 = "In Progress", "All Phases", IF(AND(Status2 = "Complete", Status15 = "Complete", Status116 = "Complete", Status159 = "Complete", Status194 = "Complete", Status93 = "Complete"), "Closed", "Not Started")))))))


The issue I have is where there is more than one phases  "In Progress" as this then throws out the IMPARSEABLE error. I would like the formula to read the status as above and apply the phase based on the earliest phase still showing in progress, i.e if the status is "In Progress for both Discovery and Build, then it should show the phase as "Discovery". Can anyone help please?





Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!