Nested If Statement with If/And in it

Kelly Ospina

I'm having trouble adding If/And to a nested IF statement. Hoping someone might be able to spot where I'm going wrong. What I'm trying to do is automate a red, yellow, green, blue column. The last criteria combine criteria in two separate fields. Thank you!

=IF([Approval Status]1 = "Denied", "Red", IF([Approval Status]1 = "Pending", "Yellow", IF([Approval Status]1 = "Approved", "Blue"), IF(AND([Mardee Approval]1 = "Approved", [Approval Status]1 = "Approved") "Green", " ")))))

Paul Newcome

You are missing a comma between the end of the AND statement and "Green", and you have an extra parenthesis tacked on to the end.

Paul Newcome

You also have an extra parenthesis after "Blue".

Kelly Ospina

Thank you Paul. I tried that, and it did remove the error code. However, I'm still off somewhere because the ball still turns blue instead of green when the IF/AND conditions are triggered. The ball should be blue only if "approval status" says approved, and turn green with both "approval status" and "mardee approval" say approved. Here is a link: https://publish.smartsheet.com/214f72664303441e9b2b53277af858b8

Is it an order of operations issue, maybe? 

Thanks for your help.

 

Mike Wilday

It is an order of operations issue, because the approval status will always be approved in the case of the blue, it will never fail and fire the green. Move the IF(AND statement before the blue one and you should be good to go. If both of the conditions fail for green, it will move on to check for the blue. 

Paul Newcome

Yes. It would be an ordering issue. Once the formula finds a true value, it stops running. Try putting the Blue IF at the end or moving your AND IF to the beginning.

Paul Newcome

Beat me by a minute... Lol

Mike Wilday

This is not a race! It's a community!   

Kelly Ospina

That did it! Thank you!

Paul Newcome

Haha. I know. I just get a kick out of it when multiple people post the same thing at pretty much the same time. Hahaha 

Paul Newcome

Happy to help! 

Mike Wilday

Glad we could be of service!