Nesting Formulas

Ray Rios

I need some help with nesting formulas for the following screenshot:

What I'm trying to accomplish is an automatic change in the "Status" Column based on the following criterias:

1) If "%Complete" >= 1% and "%Complete" <= 99% and "At Risk" = 0 then "Status" = In Progress

2) If "%Complete" >= 1% and "%Complete" <= 99% and "At Risk" = 1 then "Status" = At Risk

3) If "%Complete" < 1% and "At Risk" = 0 then "Status" = Not Started

4) If "%Complete" < 1% and "At Risk" = 1 then "Status" = At Risk

Is it possible to link this many formulas into one nested formula in the "Status" Column?

Find more posts tagged with

Healthcare

Formulas

Project Management

Comments

Christine Oakes

Ray

This should work: =IF([% complete]1 = "1", "Complete", IF([At Risk]1 = 1, "At Risk", IF([% complete]1 = "0", "Not Started", "In Progress")))

Christine

Travis

Christine, good formula! As a note, it is not neccesary to add quotes around the 1 and 0 (when checking percent values).

You could also add an OR statement show "Not Started" if % Complete is blank. Here it is:

=IF([% Complete]1 = "1", "Complete", IF([At Risk]1 = 1, "At Risk", IF(OR([% Complete]1 = "0", ISBLANK([% Complete]1)), "Not Started", "In Progress")))