Status dropdown Vs % Complete calculation

LisaB:-)

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

Paul Newcome

=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()))

LisaB:-)

This is an excellent solution - thank you!

Paul Newcome

That's because the % Complete is based on the dates.

Try this...

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

L_123

Try putting this in the parent column:

=avg(children())

Paul Newcome

=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()))

LisaB:-)

This is an excellent solution - thank you!

Paul Newcome

@LisaB:-)

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!

LisaB:-)

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

Paul Newcome

Happy to help! 👍️

And likewise to you! 🎄

LisaB:-)

@Paul Newcome

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

LisaB:-)

Further assistance would be fantastic, thank you.

Paul Newcome

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

LisaB:-)

Thanks Paul.

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

Paul Newcome

Is the Status column manually populated?

LisaB:-)

Yes.

Thanks.

Paul Newcome

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

LisaB:-)

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

Paul Newcome

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