Formula to calculate 'status' based upon % Complete and a Checkbox

Options

On a project plan, we have a column for “Task Not Applicable”, and we have an automation rule that when this box is checked, the “% Complete” field changes to “100%”.

When the box is checked, the below formula changes the “Status” field to “Complete”, instead of “Not Applicable”. Can you help me write the formula so that:

“% Complete” = 1, and “Box” is not checked, then make the “Status” equal “Complete”, and if

“% Complete” = 1, and “Box” is checked, then make the “Status” equal “Not Applicable”?

Existing Formula...: =IF([% Complete]@row = 1, "Complete", IF([Task Not Applicable]@row = 1, "Not Applicable", IF(AND(OR([% Complete]@row = 0, [% Complete]@row = ""), {Capital Women's Care - Metadata Range 1} < Finish@row), "Not Started", IF(AND([% Complete]@row <> 1, {Capital Women's Care - Metadata Range 1} > Finish@row), "Overdue", "In Progress"))))

… Additionally, I no longer recall what the “ {Capital Women's Care - Metadata Range 1} “ is referencing and it doesn’t make sense to me. I’m guessing it’s supposed to be “today”. Can you help me edit that as well?

Thank you!!!

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!