IF/AND formula that looks for multiple entries

Mike Stickney

Hi,

I am trying to create a IF/AND formula to return a status color based on multiple inputs.

For example:

IF row title "Status" equals "complete" or "cancelled" or "rejected", then column titled "RISK" (the column with the formula) should be blank. IF row titled "Status" equals "in progress", then column "RISK" should be "green".

Here is an the example formula:

=IF(AND(Status@row <> "Complete", [Launch Date]@row < TODAY()), "Red", IF(AND(Status@row = "Complete", [Kickoff Date]@row < TODAY()), "", IF(AND(Status@row = "Cancelled", [Kickoff Date]@row <> TODAY()), "green", IF(AND(Status@row = "Rejected", [Kickoff Date]@row < TODAY()), "", IF(AND(Status@row <> "In Progress", [Kickoff Date]@row < TODAY(+5)), "Yellow", IF(AND(Status@row <> "Complete", [Launch Date]@row < TODAY(+5)), "Yellow", IF(AND(Status@row <> "In Progress", [Kickoff Date]@row < TODAY(+5)), "Yellow", "Green")))))))

This works as expected except if status = "Cancelled" or "Rejected".

Thanks in advance for any insight.

Mike Stickney

Reviewing my question...

My assumption is the formula isn't working because the condition of <> "complete" already is met, so what I'm looking to do is to make it some kind of an "or" statement (so if it equals "completer" or "in progress", etc).

Hope that clarifies my question.

Ryan Sides

Hi Mike, your first If Statement says that if anything other than "Complete" and Launch Date is before today, then mark it red.

So when the Launch Date is before today and the status is "Cancelled", it's going to mark it red. Is that correct?

If not, what were you expecting it to look at next?

Ryan

Mike Stickney

Thanks for your response Ryan...

I tried to clarify in my comment, that I expected that was the reason it was happening.

Maybe a better question is instead of the first statement saying not equal (<>) how can I make it say "equal to "Complete" OR "In Progress" (or anything something).

Let me know if that is a little more clear of what my problem is.

Thanks

Michael Culley

One mistake I noticed was in this line:

[Kickoff Date]@row <> TODAY()), "green"

Green needs to be capitalized there.

Michael Culley

@Mike Stickney

Also Mike if you reorder your if statements that can fix your issue.

The formula always starts with the first if. If the condition is met, then it does that and ignores the rest.

The order matters in this case.

Take this for an example:

x = 5

if x is less than 25, "Green", if x is less than 10, "Yellow", If x is less than 6, "Red".

In that example, the formula will produce "Green" even though x meets all those conditions.

That's because the first condition was true, so the rest is ignored.

This would need to be reworded to this:

if x is less than 6, "Red', if x is less than 10, "Yellow", if x is less than 25, "Green".

This would produce "Red" as the answer.

Michael Culley

Long story short, move your cancelled and rejected to the beginning of your formula.