Checkbox formula using =IF(OR

IBRIEOG

Hi,

I am trying to find a formula to check a box based on another cell in the row having either maintenance OR support (along with other text) but keep getting an #INCORRECT ARGUMENT SET error.

 

The formula works perfectly for one criteria i.e. =IF(FIND("Maintenance", $[Item description]@row) >= 1, 1, 0) but when I try to add the second FIND using the OR function I get an error message.

 

Please could someone advice where I am going wrong with this formula or if there is an alternative way to get to the desired outcome  - a checked box if either maintenance or support are present in the item description column

 

=IF(OR(FIND("Maintenance", $[Item description]@row) >= 1, 1, 0, FIND("Support", $[Item description]@row) >= 1, 1, 0))

 

Thank you

Paul Newcome

That would be because of your syntax. When building an IF statement with an OR function, the OR will be your criteria, then value if true, then value if false.

 

=IF(logical_statement, value_if_true, [value_if_false])

 

The OR function also requires logical statements

 

OR(logical_statement_1, logical_statement_2)

 

So it gets nested in the first portion of the IF statement.

 

=IF(OR(this is true, or this is true), do this, otherwise do something else)

.

=IF(OR(FIND("Maintenance", $[Item description]@row) >= 1, FIND("Support", $[Item description]@row) >= 1), 1, 0)

IBRIEOG

Perfect - Thank you for your explanation and solution

Paul Newcome

Happy to help! 

Issa M

Hi @Paul Newcome

Thanks for the explanation above. I'm trying to adapt your formula for a scenario where I have 3 terms to search for but I keep getting an unparseable error message for some reason.

What if, for example, I want to accomplish the same exact thing as IBRIEOG except with a third word --"Rebuild"?

=IF(OR(FIND("Maintenance", $[Item description]@row) >= 1, FIND("Support", $[Item description]@row) >= 1), FIND("Rebuild", $[Item description]@row) >= 1) 1, 0)

Why is this not working?

Thanks!

Paul Newcome

@Issa M

The problem is an extra closing parenthesis that closes out the OR function before your third FIND and a missing comma after the OR function closes out before you enter the "value if true" portion of the IF statement. Try this instead...

=IF(OR(FIND("Maintenance", $[Item description]@row) >= 1, FIND("Support", $[Item description]@row) >= 1, FIND("Rebuild", $[Item description]@row) >= 1), 1, 0)

Gail Friedman

@Paul Newcome

I have a similar situation where I am trying to change status health. I want the health color to change from green to yellow if a support ticket has been open more than 2 days and red if it has been open longer than 3 days. My formula keeps returning a value of #UNPARSEABLE. The formula I am using is:

=IF(OR([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 2), "Yellow"', [Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 3), "Red", "Green")

It works with one value, but when I add the second (Red, Green) I get the error message.

Thank you for any help you can provide

Paul Newcome

@Gail Friedman

It is because you need to open additional IF statements (I am also going to change your OR to an AND statement).

IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 3), "Red"

IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 2), "Yellow"

These are your two IF statements individually (the final "Green" output will be added in at the end).

When we nest them, we take the second IF and drop it into the "else" portion of the first IF then close them both out at the end of the formula. Give this a try and see how it works for you.

=IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 3), "Red", IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 2), "Yellow", "Green"))

Gail Friedman

Thank you for your quick response. I have it working.

Paul Newcome

@Gail Friedman Happy to help! 👍️

Issa M

Awesome. Thank you, Paul!

Paul Newcome

@Issa M Happy to help! 👍️

Christina Oliver

@Paul Newcome can you help me? I am trying to get a Done box checked if my Status column = Done or N/A.

Can you tell me what is wrong with my formula? =IF(OR(Status23= "Done"), 1, 0), Status23= "N/A", 1, 0

Paul Newcome

@Christina Oliver Try this instead...

=IF(OR(Status@row= "Done", Status@row = "N/A"), 1, 0)

Christina Oliver

@Paul Newcome THANK YOU!!

Paul Newcome

@Christina Oliver Happy to help. 👍️