Calculate % Complete at Child Level AND Parent Level in one formula
I want to use the following formula as a column formula but have the % complete roll up to the parent level. See formula and snapshot as it appears now. I want Q3 and July Promotions fields to reflect a percentage completion based on the children rows. I can't seem to make it work!
% Complete formula:
=IF(Status@row = "Done 🔵", "100%", IF(Status@row = "Approval Pending 🟡", "50%", IF(Status@row = "Scheduled 📅", "75%", "0%")))
Best Answer
-
My apologies. I missed an issue with the child level formula. Quotes around numbers will output text strings that just look like numbers. Since they are text strings, they cannot be summed, averaged, etc.. You will need to remove the quotes and output the appropriate number.
Having said that... Smartsheet reads percentages as a part of one whole or a decimal of 1.
100% = 1
75% = 0.75
50% = 0.50
25% = 0.25
0% = 0
Answers
-
Couldn't you just use the same formula on the parent rows since the Status column is also being populated on those rows, or are you looking for more of an average on the parent rows?
-
I'm looking for the parent rows to reflect an average of the children rows.
I've been manually changing the status of the parent rows, but once I get the % complete to reflect correctly on those parent rows, I might do a formula to automatically change the status of the parent row based on that percentage. Does that make sense?
-
In that case you would use an AVG(CHILDREN()) piece to average the children, then you would nest both of those existing formulas in an IF statement that says if there are children, then average them, otherwise run the child row formula.
=IF(COUNT(CHILDREN())> 0, AVG(CHILDREN()), existing_child_row_formula)
-
That got me further but my parent rows show as #Divide by Zero error and I can't figure out why. I even replaced the "0%" value I had for New projects with 10% because I thought that was the culprit. I don't want to use the iferror formula because I want them to populate the correct average of the children rows. Here's my formula and a snapshot of the project plan. I have a level row if I need to reference that?
=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Status@row = "New ✨", "10%", IF(Status@row = "Postponed/Cancelled 🔴", "100%", IF(Status@row = "N/A", "100%", IF(Status@row = "In Progress 🟢", "25%", IF(Status@row = "Done 🔵", "100%", IF(Status@row = "Approval Pending 🟡", "50%", IF(Status@row = "Scheduled 📅", "75%", "MISSING STATUS"))))))))
This is the end of my spreadsheet. For previous quarters where every row reads "100%," the parent formula still has the error. I'm feeling brain dead at the moment!
-
My apologies. I missed an issue with the child level formula. Quotes around numbers will output text strings that just look like numbers. Since they are text strings, they cannot be summed, averaged, etc.. You will need to remove the quotes and output the appropriate number.
Having said that... Smartsheet reads percentages as a part of one whole or a decimal of 1.
100% = 1
75% = 0.75
50% = 0.50
25% = 0.25
0% = 0
-
Awesome - that worked. Thank you so much!
-
What's the complete formula using whole numbers instead of percentages? I got confused with you saying remove quotes? I'm in the same boat.
-
I am struggling to have my formula work. right now I have this:
=AVG(CHILDREN()), IF(Status@row = "Complete", 1, IF(Status@row = "In Progress", 0.25, IF(Status@row = "Blocked", 0.5, IF(Status@row = "Not Started", 0))))
Please help!
-
Sure! Here's where I netted out. I track the progress of every subtask to complete a parent task. If a subtask is marked done, scheduled, canceled or postposed, it's scored as 100% finished. If it's new, it's 0% finished and if it's in progress (25% finished) and approval pending (50% finished). Then the parent task shows an average of the children. Not perfect, but it gives us an idea of how close we are to completion on a project. Hope this helps!
=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Status@row = "New ✨", 0, IF(Status@row = "Postponed 🟤", 1, IF(Status@row = "Canceled ⚫", 1, IF(Status@row = "In Progress 🟢", 0.25, IF(Status@row = "Done 🔵", 1, IF(Status@row = "Approval Pending 🟡", 0.5, IF(Status@row = "Scheduled 📅", 1, " "))))))))
-
Thank you!
I get a # DIVIDE BY ZERO message. Here's my formula:
=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), =IF(Status@row = "Complete", 1, IF(Status@row = "In Progress", 0.25, IF(Status@row = "Blocked", 0.5, IF(Status@row = "Not Started", 0, "")))))
What am I missing? Just need to show the percent complete for the whole section of the Communication parent row from its four children rows. @Paul Newcome, are you able to advise?
-
-
Thank you Paul. I don't know what that means. I'm sorry! I thought it is a column formula? what's the difference?
-
I did this
and got this!
-
Help Article Resources
Categories
Check out the Formula Handbook template!