Multiple IF Conditions

[nausherc]

Hi Experts!

I am trying to formulate a column using the reference formula from an excel template, but due to some reasons, the output is not what is expected.

This is in the excel- =IF(M2=12345;IF(S2<>0;S2;R2);IF(M2=67890;IF(S2<>0;S2;R2);IF(IF(B2="Sometext";R2;S2)<>0;IF(B2="Sometext";R2;S2);D2)))

This is what I did using formula tool- IIF([M1]=12345, IF [S1]!=0 THEN [S1.] ELSE [R1] ENDIF, IIF([M1]=67890, IF [S1]!=0 THEN [S1] ELSE [R1] ENDIF, IIF(IF [Sub Category]='Sometext' THEN [R1] ELSE [S1]!=0 ENDIF,[S1], [D1])))

Would you be able to help? Thanks

Regards.

[AdamR_AYX]

I think it should be this

IIF(M2=12345,IIF(S2!=0,S2,R2),IIF(M2=67890,IIF(S2!=0,S2,R2),IIF(IIF(B2="Sometext",R2,S2)!=0,IIF(B2="Sometext",R2,S2),D2)))

IF(X;Y;Z) in excel becomes IIF(X,Y,Z) in Alteryx

[nausherc]

I get "Invalid type operator!=." upon selecting the last column (D2). The column D2 is also a formulated field, can that be the reason?

[AdamR_AYX]

So you need to check the data types in your comparisons.  I think m2, s2 and r2 need to be numeric types and b2 and d2 string types.

[Kaviyarasan_P · Accepted solution]

Hi,

I believe you will answer for your query

if ([M2]= 12345) then (if [S2] != 0 then [S2] else [R2]
endif) else (if [M2]=67890 then (if ([S2]!= 0) then [S2] else [R2] endif) else (if (if([B2 ]='Sometext')then [R2] else [S2] endif) != 0 then (if([B2 ]='Sometext')then [R2] else [S2] endif) else[D2] endif)endif) endif

[nausherc]

I just did that and the error is gone now. However, D2 is not referred, if conditions are not met from S2. Some cells contain numbers in D2, which I would want to be picked when S2 is [Null]. It's not doing that. Data type issue?

[AdamR_AYX · Accepted solution]

Another thing that might help you are the ToString and ToNumber formula methods.

[nausherc]

I just added another column and used if isnull, isnumber to get the desired output. Thanks for all the help!