Formula Help: Combining Checking a Box with a % Complete to Return a Status Ball

Renata Franklin

I'm having a hard time combining a formula that when I check off the "Done" checkbox it turns the % Complete to %100 and also changes the health to green as well as combining the criteria of this formula: =IF([% Complete]12 < 0.5, "Red", IF([% Complete]12 < 1, "Yellow", "Green"))

Is this possible?

Paul Newcome

You would need two separate formulas. It looks like the formula you have posted should be working to generate the green, so you should only need to enter another formula into the [% Complete] column.

Renata Franklin

Thanks! The main problem is I can get them both to work on their own but not simultaneously. It's like the checkbox formula is dominant over the health status formula.

Paul Newcome

I don't understand. They need to be separate since they are operating two different cells.

Renata Franklin

Correct, they are both separate formulas in two different cells. The check box formula works just fine, no problem. But when I change the percentage of the task above 50% the health should change to yellow and it doesn't. If I delete the checkbox formula than the health formula operates as I need it to. Does that make more sense?

Paul Newcome

It makes sense what you are saying now, but it doesn't make sense why it is working that way because the RYG formula does not reference the checkbox.

Can you provide a screenshot of the sheet with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed? Can you also copy/paste the formulas directly from your sheet to here as well?