RAG indicator at parent level Vs Child

Vikas

Hi All,

I need help on a formula. In my current schedule the RAG is determined by % complete of task, desired% as on today and low tolerance. Here the parent level is indicating RED although child is in green and blue but no RED. Probably due the Desired % is more than the actual % but the actual % for child task is more than desired % and is in Green.

Here Smart sheet automatically calculates the actual % at parent level so the gaps seems to be between Desired % and Actual %.

Question: What will be the right solution to this, do I have to fix the Desired % complete or RAG formula and how.

Below are the formulas I have used and attached the screen shot of my schedule.

RAG: =IF(Start@row <> "", IF([% Complete]@row = 1, "Blue", IF(AND(Start@row > TODAY(), [% Complete]@row = 0), " ", IF(Duration@row = 0, " ", IF([% Complete]@row >= [Desired % Complete]@row, "Green", IF([% Complete]@row >= [Low Tolerance]@row, "Yellow", "Red"))))))

Desired % Complete:=[Days elapsed since Start]@row / Duration@row

Low Tolerance:=0.9 * ([Desired % Complete]@row)

Days elapsed since start:=NETWORKDAY(Start@row, TODAY())

Julio S.

Hi @Vikas,

It looks like you'd like to base the results of the parent rows in the RAG values from their children rather than for the Column formula to perform the same calculation in parent rows than in the childrenand that the Parent Rollup functionality might be causing the current behavior as parent rows display values corresponding to a weighed average of their children. 

A possibility to avoid this could be to add Helper Column to your sheet that aren't used in Dependencies so that they can contain individual values of your choice that won't calculate automatically based on their children and then update the references in your formula to look at these columns instead of the ones used as Project Columns.

If you'd like to keep the current project structure without adding new columns and updating the references, you may want to add separate logic to your formula to perform different operations based on wether the column is parent or child. Below you can see an example of this but note how this can make the formula become lengthy and overly complex:

 =IF(COUNT(CHILDREN([Milestone / Deliverable / Task]@row) > 0, IF(COUNTIF(CHILDREN(RAG@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(RAG@row), "Blue") > 0, "Blue", "Green")), YOUR CURRENT FORMULA HERE) 

Because there are Columns in your formula that aren't displayed in your screenshots, if you'd need further advise, I'd suggest adding new screenshots where all Columns involved are displayed.

I hope that this can be of help.

Cheers!

Julio