Planned to Actual RYG

Deena B

Seeking help on a formula to show RYG. I was getting an unparseable error, however most recently receiving errors regarding syntax.

I want show the following:

If % complete is 100%, Green

If today is greater than finish date AND % complete < 1, Red

If % complete is < 50%, Yellow else Green

I have looked at this until I'm blue in the face, so apologize if this is a simple oversight!

Thank you!

Julie Fortney

Hi @Deena B, I don't think you can use the average function in a symbols column. But you can set criteria for your child row symbols, then determine the criteria for parent row symbols.

For child tasks:

If % complete is 100%, Green

If today is greater than finish date AND % complete < 1, Red

If % complete is < 50%, Yellow else Green

For parent tasks:

You could decide that if any child tasks are red, the parent should be red. If there are more yellow child tasks than green, make the parent yellow. If there are no red tasks and the majority of them are green, make the parent green.

(This is just one potential scenario; the criteria is up to you.)

Once you decide on the criteria, I'm happy to help with the formula.

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

Deena B

Thanks for the feedback @Julie Fortney!

I like your approach on both child and parent tasks, and would appreciate your help on a formula. Thank you in advance!

Julie Fortney

Hi @Deena B

Try this formula:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Green"), "Yellow", "Green")), IF([% Complete]@row = 1, "Green", IF(Finish@row < TODAY(), "Red", IF(AND([% Complete]@row < 0.5, Start@row < TODAY()), "Yellow", "Green"))))

This formula encompasses a small change to the child task status rules:

If % complete is 100%, Green

If today is greater than finish date AND % complete < 1, Red

If % complete is < 50% AND the start date is in the past, Yellow else Green

Let me know if this works for you!