Nested Formulas

bagoston

I created the below individual formulas and tested them. They all work independently. However, I want to combine them into a single formula so it can automatically update my Status column.

Not Started

=IF(AND([% Complete]@row = 0, [Finish]@row > TODAY()), "Not Started")

In Progress

=IF(AND(VALUE([% Complete]@row) > 0, VALUE([% Complete]@row) < 1, Finish@row > TODAY()), "In Progress", "")

Behind

=IF(AND(VALUE([% Complete]@row) < 1, Finish@row < TODAY()), "Behind")

Completed

=IF([% Complete]@row = 1, "Complete")

Blocked

=IF([Blocked]@row = true, "Blocked")

On Hold

=IF([On Hold]@row = true, "On Hold")

Canceled

=IF([Canceled]@row = true, "Canceled")

At Risk

=IF([At Risk]@row = true, "At Risk")

Combined Formula

For the combined formula, I have gotten it to work for Not Started, In Progress, Behind, and Completed. However, I cannot get it to work when I add Blocked, On Hold, Canceled, and At Risk formulas.

Below is the current combined formula that is working through Completed.

=IF(AND([% Complete]@row = 0, Finish@row > TODAY()), "Not Started", IF(AND(VALUE([% Complete]@row) > 0, VALUE([% Complete]@row) < 1, Finish@row > TODAY()), "In Progress", IF(AND(VALUE([% Complete]@row) < 1, Finish@row < TODAY()), "Behind", IF([% Complete]@row = 1, "Complete"))))

Any help in adding the additional formulas for Blocked, On Hold, Canceled, and At Risk would be appreciated.

Smartsheet for Nested Formula.jpg

Steve Rohrdanz

Try this (assuming the Blocked, On Hold, Canceled, and At Risk columns are checkboxes):

=IF(Blocked@row = 1, "Blocked", IF([On Hold]@row = 1, "On Hold", IF(Canceled@row = 1, "Canceled", IF([At Risk]@row = 1, "At Risk", IF(AND([% Complete]@row = 0, Finish@row > TODAY()), "Not Started", IF(AND(VALUE([% Complete]@row) > 0, VALUE([% Complete]@row) < 1, Finish@row > TODAY()), "In Progress", IF(AND(VALUE([% Complete]@row) < 1, Finish@row < TODAY()), "Behind", IF([% Complete]@row = 1, "Complete"))))))))

Steve Rohrdanz

Try this (assuming the Blocked, On Hold, Canceled, and At Risk columns are checkboxes):

=IF(Blocked@row = 1, "Blocked", IF([On Hold]@row = 1, "On Hold", IF(Canceled@row = 1, "Canceled", IF([At Risk]@row = 1, "At Risk", IF(AND([% Complete]@row = 0, Finish@row > TODAY()), "Not Started", IF(AND(VALUE([% Complete]@row) > 0, VALUE([% Complete]@row) < 1, Finish@row > TODAY()), "In Progress", IF(AND(VALUE([% Complete]@row) < 1, Finish@row < TODAY()), "Behind", IF([% Complete]@row = 1, "Complete"))))))))

bagoston

You are the MAN! Worked perfectly!