# Status dropdown Vs % Complete calculation

✭✭

Hi all, I'm hoping you can help me with a thorny problem.

I have a 'Status' column, which is a dropdown which contains 'Not Started'/ 'In Progress'/ 'Completed'. I was also using the '% Completed' column with dependencies to rollup the child rows and give a percentage total in the parent rows.

My users asked if '% Complete' could be automatically updated to '100%' if 'Complete' was selected in the 'Status' column, which I enabled by using this formula in '% Complete'

=IF(Status6 = "Complete", 1, 0)

All was going fine, until I realised that by doing this, I had disabled the parent row rollup feature. My parent rows are now all stuck at 0%. If I use 'Sum Children' then it just adds up all the 100%s in the child rows and displays a total of '800%'.

I think I need to reinstate system column of '% Complete', but then that would mean we're back to updating both 'Status' and then '100%' on every row. Can anyone help me with any bright ideas?

Many thanks, Lisa

• ✭✭✭✭✭✭

=AVG(CHILDREN())

Will give you your parent row rollup in the [% Complete] column. So to roll that into your above...

=IF(Status@row = "Complete", 1, AVG(CHILDREN()))

• ✭✭

This is an excellent solution - thank you!

«1

• ✭✭✭✭✭✭

Try putting this in the parent column:

=avg(children())

• ✭✭✭✭✭✭

=AVG(CHILDREN())

Will give you your parent row rollup in the [% Complete] column. So to roll that into your above...

=IF(Status@row = "Complete", 1, AVG(CHILDREN()))

• ✭✭

This is an excellent solution - thank you!

• ✭✭✭✭✭✭

Happy to help! 👍️

If you are going with the above solution, please don't forget to flag it as the accepted answer. That way other people searching for a similar solution or other people searching to assist know that a solution has been presented!

• ✭✭

Didn't know about that - have done it and will do it in future. Many thanks and a Merry Christmas!

• ✭✭✭✭✭✭

Happy to help! 👍️

And likewise to you! 🎄

• ✭✭

Hi again, this formula isn't quite working, could you help me by looking at it again? The problem is it adds the number of child rows, and then works out the % by dividing that number by 100. What I really need it to do is work out the % based upon the durations. So in the screenshot below, the project actually takes 53 days, but the % is showing as 75% complete, which isn't accurate - it should about 5-6%.

Very many thanks

• ✭✭

Further assistance would be fantastic, thank you.

• ✭✭✭✭✭✭

There are a few ways to tackle this. How is the % Complete in the child rows being populated?

• ✭✭

Thanks Paul.

=IF(Status25 = "Complete", 1, 0)

• ✭✭✭✭✭✭

Is the Status column manually populated?

• ✭✭

Yes.

Thanks.

• ✭✭✭✭✭✭

The formula in the Child rows of your % Complete column will need to account for duration then to be able to have it rolled up and accounting for duration on the parent rows.

This would mean replacing the 0 in your current formula with something along the lines of...

=IF(Status@row = "Complete", 1, IF((TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row) < 1, (TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row), 1))

• ✭✭

Could I just check - that formula needs to be pasted into the child rows? many thanks, Lisa.

• ✭✭✭✭✭✭

Correct. Then your =AVG(CHILDREN()) in the parent rows should work the way you want it to.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!