Multiple If function in one formula

[allexchadha]

I am trying to work on one formula already used in Excel but i am not able to implicate the same in one formula into Alteryx. Formula used in Excel is: =IF(R18<0, IF(Q18<0,Q18+R18,Q18+0),Q18+R18) and one more formula is: =IF(OR(F18=101,F18=1993),IF(I18<0,-0.0625,0.375),IF(I18<0,0.375,-0.0625))/100 I have already spilit these formulas and generate the results but i wanted to run in one go. Thanks for your help in advance! Allex Chadha

[lmorrell · Accepted solution]

Hi @allexchadha 

Solution is attached.

The IF(a,b,c) formula translates to "If a is true, then b, else c". By taking this into Alteryx we can run each formula as a single formula via nested "if" statements. 

So the first statement becomes

IF [R18] < 0 
then (IF [Q18]<0 then [Q18]+[R18] else [Q18]+0 endif) 
else Q18+R18
endif

and the second statement becomes

( 
if [F18] = 101 OR [F18] = 1993 
then 
	if [I18] < 0 then -0.0625 else 0.375 endif
else 
	if [I18] < 0 then 0.0375 else -0.0625 endif
endif 
) 

/

100

One thing to note though: Where your current formula has cell values (eg. Q18, R18, F18, I18) you will want to place the column name in square brackets.

If you have any issues, please provide some sample data with your column names and we can get it working. 

Hope this helps! 

[geraldo · Accepted solution]

Hi,

You can substitute for "IIF" without altering your logic and changing the position of "OR".

See the example:

IIF(R18<0, IIF(Q18<0,Q18+R18,Q18+0),Q18+R18)

IIF((F18=101 OR F18=1993),IIF(I18<0,-0.0625,0.375),IIF(I18<0,0.375,-0.0625))/100

[]

[estherb47]

Hi @allexchadha 

You can take a number of approaches, as shown by the other answers here. Instead of referencing cells in Alteryx, you're referencing columns. You could try these too.

IF [R]<0 AND [Q]<0 THEN [Q]+[R]

ELSEIF [R]<0 THEN [Q]+0

ELSE [Q]+[R] 

ENDIF

(IF [F] In (101,1993) AND [I]<0 THEN -0.0625

ELSEIF [F] In (101,1993) THEN 0.375)

ELSEIF [I]<0 THEN 0.375

ELSE -0.0625))

ENDIF)/100

Let me know if that helps.

Cheers!

Esther

[allexchadha]

Thanks for providing the solution. I have used the second formula and it is working perfectly.

Many Thanks,

Allex Chadha

[allexchadha]

Thanks for providing the solution. I have used first formula and it is working perfectly fine. In second formula i am not sure what is going wrong and i am not getting the correct result. However i have used another formula and it worked fine.

Thanks for you help 🙂

Regards,

Allex Chadha