HELP Nested-if Formula

Stefanie
Stefanie
edited 12/09/19 in Smartsheet Basics

Hi there, 

I'm trying to complete my certification and the following nested-if formula needs to be created:

"Use a nested-if formula to automate the RYG light in the "Status "column, depending on the "% Complete" for each request

If a request is less than 70% complete, turn the Status column "Red"

If a request is 70% or above and less than 100% complete, turn the Status column "Yellow"

If a request is 100% complete, turn the Status column "Green""

This is what i came up with (and other variations) but it doesn't work and I don't know why and I am going crazy! Help please!

=IF([Creative Request]4 < 0.7, "Red", IF(AND([Creative Request]4 >= 0.7, [Creative Request]4 < 1, "Yellow", IF([Creative Request]4 = 1, "Green"))))

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You need to close your AND statement before you move on to the THEN statement. IF(AND(This, That), Then, IF(This, Then

  • Chak Khiam
    Chak Khiam ✭✭✭✭

    Suggestion:

    It might be easier to see, if you break the formula, as such

    =IF([Creative Request]4 < 0.7, "Red",

         IF(AND([Creative Request]4 >= 0.7, [Creative Request]4 < 1, "Yellow",

             IF([Creative Request]4 = 1, "Green"))))

    You should be able to spot the missing close bracket in the second line of the formula - as highlighted by Mike.

     

    The formula addresses the range and their conditions are as follows:

    ----------------------------------------------------70%----------------100%

    <---------------------(Red)---------------- ----->|<------Yellow------>|

                                                                                                  v

                                                                                              Green

     

    Whilst the formula follows the three stages in a "left to right" sequence, we could simplify the formula and to make it more efficient with the same results.

    We could achieve this with this logic. Have the formula to check if the cell contains 100% and if it does then "Green". If not, then check for if it is less than 75% and if it is then "Red", else "Yellow" - that is:

    IF(Request@row=1, "Green"

         IF(Request@row<0.7, "Red", "Yellow"))

     

    All the conditions are covered and formula calculations are more efficient in arriving at the desired results.

    =IF(Request@row = 1, "Green", IF(Request@row < 0.7, "Red", "Yellow"))

     

              

     

     

     

  • Thank you Mike and Chak, I really appreciate your help with this!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Absolutely, I am glad we could help you get that figured out. 

  • Tracy G
    Tracy G ✭✭

    =IF([% Complete]3 < 0.7, "Red", IF(AND([% Complete]3 >= 0.7, [% Complete]3 < 1), "Yellow", IF([% Complete]3 = 1, "Green")))

  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭
    edited 12/15/19

    Re-posting the answer from @Tracy G using the newly available code format:


    =IF([% Complete]3 < 0.7, "Red", IF(AND([% Complete]3 >= 0.7, [% Complete]3 < 1), "Yellow", IF([% Complete]3 = 1, "Green")))

  • =IF([% Complete]@row = 1, "Green", IF([% Complete]@row >= 0.7, "Yellow", "Red"))

    This one works as well without using the AND function. Simpler