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?

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")))