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.

• ✭✭✭✭✭✭

Try:

=MAX(CHILDREN())

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Thank you Mark. But, I get 0%.

• There are formulas in the children rows. Could that be the problem? It's reading a formula and not the number itself?

• I was able to fix it. Thank you!

• ✭✭✭✭✭✭

Excellent. Glad you figured it out. Please accept an answer to close the discussion. Thank you for contributing to the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!