How can I unflag tasks that have a status of 'Cancelled'?

Question

I am looking to add a new condition to my At Risk column formula so that cancelled tasks are not flagged. My current formula flags all tasks that are past the completion date and are 100% complete. I want to keep it so that the ideal completion date and % complete is still visible so I still have that data, but I do not want "Cancelled" tasks to be shown at risk.

Current formula:

=IF(AND([% Complete]@row < 1, [Ideal Completion Date]@row < TODAY(), NOT(ISBLANK([Ideal Completion Date]@row))), 1, IF(COUNTIF(DESCENDANTS([At Risk]@row), =1) > 0, 1, 0))

Eric Law · Accepted Answer

@courtney.w You need to put the Cancelled portion of the formula first.

=IF( [Ideal Completion Date]@row = "Cancelled", 0, IF(AND([% Complete]@row < 1, [Ideal Completion Date]@row < TODAY(), NOT(ISBLANK([Ideal Completion Date]@row))), 1, IF(COUNTIF(DESCENDANTS([At Risk]@row), =1) > 0, 1, 0)))