# Planned to Actual RYG

Options
✭✭

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!

Tags:

## Answers

• Overachievers
edited 01/06/23
Options

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

• ✭✭
Options

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!

• Overachievers
Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!