How can I use IF to get a % in the topmost row in a hierarchy?

The issue is that of the 4 lines there are two options based on level of completeness. I've come up with this, but it's not working. =IF(Status4 = "In Process", "10%", IF(Status4 = "Complete", "20%", IF(Status5 = “In Process”, “30%”, IF(Status5 = “Complete”, “60%”, IF(Status6 = “In Process”, “70%”, IF(Status6 = “Complete”, “90%”, IF(Status7 = “In Process”, “95%”, IF(Status7 = “Complete”, “100%”))))))))

However, is there a way to just use the numbers in the % Complete column to do this? In the example above, I would need 95% to show in the % Complete for the Sprint 1 row. If Rows 6 & 7 blank, then I would need 30% to show in the Sprint 1 row.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!