Status of Child rows

Vikki Cooper

Hi

I am trying to create a Project Schedule with Budget and Variance and include an overall status using the status of the child rows.

 

I have 1 line that is the overall project and then 4 phase lines, with around 20 tasks underneath each phase line. I want the phase lines to give an overall status of the child task rows and then the overall project line to give an overall status of the whole project based on what is on the all of the lines. 

I want it to work on the same principle that the RYG balls work

 

I have 

In progress

Completed

Not started

For instance if 3/4 of the status' are in progress then the parent line should state in progress. So basically if the average of the whole phase is in progress then the parent line (phase line) should state in progress and the same for the overall project line

 

Thanks in advance

Adam Overton

I think you're going to end up with a big complicated formula, so I'll break it down a bit, in part for my own benefit. Also, I'm expecting you'll end up wanting to tweak it anyway.

 

You can convert your status to a number with a formula like:

=IF(Status3="Not Started", 0, IF(Status3="In Progress", 1, 2))

 

I would make another column, let's say "Status Number" next to status to convert it to a number.

 

Now you can get the average of that and round it to the nearest one in the summary line with:

=ROUND(AVG(CHILDREN()))

 

Back in the Status summary row field you can convert back to Status:

=IF([Status Number]2 = 0, "Not Started", IF([Status Number]2 = 1, "In Progress", "Completed"))

 

Unfortunately, this will have the summary show not started for a bit after it has been started, and show completed a bit before it is actually complete, so it may need some tweaking to be what you actually want.