Need assistance with an extensive "If" statement which involves two separate columns

Question

I am utilizing "formula" to add a new column which will state "Y" or "N" based on logic:

if([award basis] = "Competitive" or "Open Competitive" and (!contains[Billing Method], "CP" or "TM" or "LH")
then "Y"
Else "N"
Endif

The logic is if award basis is either "Competitive or open competitive, and the field billing method does not contain "CP" "TM" or "LH", the new field should show "Y", otherwise "N".

This breaks after the "and" statement.

ChrisTX · Answer

👆 This syntax is missing parenthesis around the multiple Contains functions, which may produce unexpected results when mixing AND and OR.

Raj · Answer

@JoshuaElphee Just a bit of Structuring and your formula will be working.

IF([award basis] = "Competitive" OR [award basis]="Open Competitive")

And (!contains([Billing Method], "CP")
OR !contains([Billing Method],"TM")
OR !contains([Billing Method],"LH")
THEN "Y" Else "N"
Endif

alexnajm · Answer

if([award basis] = "Competitive" or [award basis]="Open Competitive") and (!contains([Billing Method], "CP") or !contains([Billing Method],"TM") or !contains([Billing Method],"LH")
then "Y"
Else "N"
Endif