Ignore Parent Records formula & Ranked graph

M. David

Hello. I am trying to create a graph that shows the top 10 tasks ranked by the variance between Initial Duration and Actual Duration. To make this work, I want to ignore tasks that are parent records. What would this formula look like? I have created a formula to rank the Variance; =RANKEQ(Variance1, Variance:Variance, 1). I also added a column with formula to show the level of parent/child it is. On the pic below, the second column is the Variance (in months), the third is the "Levels" column, and the fourth is the "Variance Rank" column. 

As I've looked at a solution, the issue I run into is how to treat each task individually. I want "Site Utilities" and "Footings for Core" to be considered in the ranking even though they are of different levels in the parent/child levels. 

All help is appreciated. Thanks.

Paul Newcome

If you change your Levels column to something like...

 

=COUNT(CHILDREN())

 

It will return a zero for any rows that are not a parent row regardless of what level of hierarchy they are on.

.

Then you could use a JOIN/COLLECT/LARGE combination to pull together all rows that are ranked as the first highest, second highest, etc. along the lines of

 

=JOIN(COLLECT(Task:Task, Levels:Levels, @cell = 0, [Variance Rank]:[Variance Rank], @cell = LARGE([Variance Rank]:[Variance Rank], 1)), ", ")