I am working on a project plan template for a colleague and they want the health column to be dependent on variance. The issue is they will struggle to write or edit formulas and the variance column doesn't appear until baselines are set.
Has anyone come up with a solution for this?
The current formula I have been testing is below. It works until I copy that formula into a sheet with no variance column. It doesn't start working once the correct columns are added in. (Note: [Today - End Date]@row is a formula that subtracts the target End Date from Today's Date.
=IFERROR(IF(OR(AND(Status@row = "Complete", Variance@row > 10), AND(NOT(Status@row = "Complete"), [Today - End Date]@row > 10)), "Red", IF(OR(AND(NOT(Status@row = "Complete"), [Today - End Date]@row > 4), AND(Status@row = "Complete", OR(Variance@row > 0, Variance@row < 4))), "Yellow", IF(AND(Status@row = "Complete", Variance@row <= 0), "Green", "Gray"))), "")