Nested If Statement with If/And in it

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

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.