Calculated column help

%3CLINGO-SUB%20id%3D%22lingo-sub-1017639%22%20slang%3D%22en-US%22%3ECalculated%20column%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017639%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIF(%5BChemical%20Name%5D%3D%22Dioxane%22%2C%22Yes%22%2CIF(%5BChemical%20Name%5D%3D%22Tetrahydrofuran%22%2C%22Yes%22%2CIF(%5BChemical%20Name%5D%3D%222-Propanol%22%2C%22Yes%22%2C%22%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20an%20easier%20way%20to%20write%20the%20function%20above%3F%26nbsp%3B%20I%20have%20a%20list%20of%2020%20chemicals%20that%20I%20would%20like%20this%20column%20to%20%22search%20for%22%20and%20show%20a%20%22Yes%22%20if%20it%20finds%20a%20match%20to%20%5BChemical%20Name%5D.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1017639%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFunction%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1018282%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20column%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1018282%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462125%22%20target%3D%22_blank%22%3E%40gopalaraoa%3C%2FA%3E%26nbsp%3B%20Still%20long%20but%20you%20can%20use%20math%20and%20not%20have%20to%20deal%20with%20lots%20of%20parentheses...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIF(%5BChemical%20Name%5D%3D%22Dioxane%22%2C1%2C0)%20%2B%26nbsp%3BIF(%5BChemical%20Name%5D%3D%22Tetrahydrofuran%22%2C1%2C0)%20%2B%26nbsp%3BIF(%5BChemical%20Name%5D%3D%222-Propanol%22%2C1%2C0)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThen%2C%20wrap%20that%20whole%20thing%20in%20an%20if%26nbsp%3B%3C%2FSPAN%3Estatement%3C%2FP%3E%3CP%3E%3CSPAN%3EiF(theAboveVeryLongStatement%20%26gt%3B%200%2C%20%22Yes%22%2C%20%22%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

=IF([Chemical Name]="Dioxane","Yes",IF([Chemical Name]="Tetrahydrofuran","Yes",IF([Chemical Name]="2-Propanol","Yes","")))

 

Is there an easier way to write the function above?  I have a list of 20 chemicals that I would like this column to "search for" and show a "Yes" if it finds a match to [Chemical Name]. 

1 Reply
Highlighted

@gopalaraoa  Still long but you can use math and not have to deal with lots of parentheses...

 

IF([Chemical Name]="Dioxane",1,0) + IF([Chemical Name]="Tetrahydrofuran",1,0) + IF([Chemical Name]="2-Propanol",1,0)

 

Then, wrap that whole thing in an if statement

iF(theAboveVeryLongStatement > 0, "Yes", "")