Calculate % Complete at Child Level AND Parent Level in one formula

HH_BE1894

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%")))

Paul Newcome

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

Paul Newcome

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?

HH_BE1894

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?

Paul Newcome

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)

HH_BE1894

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!

Paul Newcome

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

HH_BE1894

Awesome - that worked. Thank you so much!

Paul Newcome

Happy to help. 👍️