HELP Nested-if Formula

edited 12/09/19 in Using Smartsheet
06/28/19 Edited 12/09/19
Answered - Pending Review

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 WildayMike Wilday ✭✭✭✭✭

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

  • 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([email protected]=1, "Green"

         IF([email protected]<0.7, "Red", "Yellow"))

     

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

    =IF([email protected] = 1, "Green", IF([email protected] < 0.7, "Red", "Yellow"))

     

              

     

     

     

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

  • Mike WildayMike Wilday ✭✭✭✭✭

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

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

  • Gwyneth CGwyneth C admin
    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")))

Sign In or Register to comment.