Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Help with nested If formulat
These formulas work independently. I am attempting to combine them into one nested if formula.
1) if the %complete is full, status is green: =IF([% Complete]10 = "Full", "Green" , "Red")
2) if the finish date is greater thant today, status is green: =IF(Finish10 > TODAY(), "Green")
3) if %complete is full and finish date is less than today, status green: =IF(AND([% Complete]10 = "Full" , Finish10 < TODAY()) , "Green")
4) if finish date is greater than today and less than today +30 (within 30 days), status is yellow: else is red. =IF(AND(Finish10 > TODAY(), Finish10 < TODAY() + 30), "Yellow", "Red")
5) if % complete is empty, quarter, half, then red. if % complete is three quarter, then yellow. if % complete is full, green. =IF([% Complete]10 = "Empty", "Red", IF([% Complete]10 = "Quarter", "Red", IF([% Complete]10 = "Half", "Red", IF([% Complete]10 = "Three Quarter", "Yellow", IF([% Complete]10 = "Full", "Green")))))
I tried this and I get an unparseable error....
=IF([% Complete]10 = "Full" , "Green" , IF(Finish10 > TODAY(), "Green" , IF(AND([% Complete]10 = "Full" , Finish10 < TODAY()), "Green" , IF(AND(Finish10 > TODAY() , Finish10 < TODAY() + 30), "Yellow" , IF([% Complete]10 = "Empty" , "Red" , IF([% Complete]10 = "Quarter" , "Red" , IF([% Complete]10 = "Half" , "Red" , IF([% Completed]10 = "Three Quarter" , "Red" , IF([% Complete]10 = "Full" , "Green")))))))))
Comments
-
Here you go:
=IF([% Complete]10 = "Full" , "Green" , IF(Finish10 > TODAY(), "Green" , IF(AND([% Complete]10 = "Full" , Finish10 < TODAY()), "Green" , IF(AND(Finish10 > TODAY() , Finish10 < TODAY() + 30), "Yellow" , IF([% Complete]10 = "Empty" , "Red" , IF([% Complete]10 = "Quarter" , "Red" , IF([% Complete]10 = "Half" , "Red" , IF([% Complete]10 = "Three Quarter" , "Red" , IF([% Complete]10 = "Full" , "Green")))))))))
You had "completed" instead of "complete" in one argument
Best,
Kara -
Thanks for your support Kara. I appreciate it. Small typo. So, I input the formula and tested the dates and the yellow doesn't work when the date is within 30 days. Is my logic off in terms of the order in which the nested "IF" statements will execute? Ideas? My goal is to no matter what, if the "Finish" date is within 30 days of being due, then change status to Yellow.
-
How about this - if the Status is not full, and the due date is within 30 days, the ball is yellow?
=(IF([% Complete]7 <> "Full", IF(AND(Finish7 > TODAY(), Finish7 < TODAY() + 30), "Yellow"), IF([% Complete]7 = "Full", "Green", IF(Finish7 > TODAY(), "Green", IF(AND([% Complete]7 = "Full", Finish7 < TODAY()), "Green", IF([% Complete]7 = "Empty", "Red", IF([% Complete]7 = "Quarter", "Red", IF([% Complete]7 = "Half", "Red", IF([% Complete]7 = "Three Quarter", "Red", IF([% Complete]7 = "Full", "Green"))))))))))
-
This works great for yellow status. But now, when Finish is outside of 30 days being due, the status column turns blank.
I am hoping to get this formula to work in the following order:
1) If finish > 30 days out, green status
2) if finish < TODAY() and % Complete = FULL then green
3) if finish < 30 days out, yellow status
4) Anything else is red
I may have written original formula incorrectly or too long.
-
Ok, try this:
=IF(AND(Finish23 < TODAY() + 30, [% Complete]23 = "Full"), "Green", IF(Finish23 > TODAY() + 30, "Green", IF(AND(Finish23 < TODAY() + 30, [% Complete]23 <> "Full"), "Yellow", "Red")))
Hope that helps!
Kara
-
You and Kara are very close!
I would suggest you change your criteria to the following:
1) If finish > 30 days out then green status
2) if % Complete = "FULL" then green status
(The date isn't needed to get what you want)
3) if and(finish >= today() and finish <= 30 days out then yellow status
(Using >= and <= in criteria 3 will eliminate a Red status on records where finish = today() and finish = today() + 30)
4) Anything else is red
Here's the formula I came up with:
=IF(Finish23 > TODAY() + 30, "Green", IF([% Complete]23 = "Full", "Green", IF(AND(Finish23> = TODAY(), Finish23 <= TODAY() + 30), "Yellow", "Red")))
Good Luck!
Norman
-
Thanks for your help Norman. Unfortunately I am still not seeing the behavior I am looking for. The status ball is green unless I change the % Complete to less than full. It then gives an error. However, I appreciate your logic in that it is requiring a formula in shorter length. Although at this point it isn't working quite yet. I am still stuck on this formula...!
-
Here's my take on it:
=IF(OR(Finish23 > TODAY() + 30, [% Complete]23 = "Full"), "Green", IF(Finish23 > TODAY(), "Yellow", "Red"))
Craig
-
Thank you Craig! Your formula worked perfectly. I appreciate everyone who took the time to comment and provide suggestions!
-
This seems to be along the same lines as what I am trying to do except instead of status balls I wanted words.
I am trying to format the Status Column (See screenshot) so that based on what is in the CM Review Status Column or the Designer Response Column the Status Column Changes.
I tried using multiple IF statements but I always get an unparseable error.
If one of you could help I would appreciate it, Thanks!
