HELP Nested-if Formula

Stefanie

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

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

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

 

          

 

 

 

Stefanie

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

Mike Wilday

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

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

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

Gabe Lopez

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

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