I have this if statement in excel sheet which I need to replicate in Alteryx =((IF((C13>0),(IF((H13 0 and H13 <=C13 then H13 else C13 endif if yes then where does last 0 goes.

Excel nested if statement in Alteryx

Data_Alter

I have this if statement in excel sheet which I need to replicate in Alteryx

=((IF((C13>0),(IF((H13<=C13),(IF(C13=M13,0,H13)),C13)),0)))

Am I right that this in Alteryx translates to

C13= M13 then 0

elseif C13.> 0 and H13 <=C13

then

H13

else

C13

endif

if yes then where does last 0 goes.

Data Investigation

Accepted answers

IraWatt

@Data_Alter Column I's formula is completely different from the one you initially gave:

IF((

IF((C3>0),(

IF((H3<=C3),(

IF(C3=M3,0,H3)),C3)),0)-

IF(F3<=C3, F3, 0)+

IF(C3<0, C3, 0))<=0, 0, (

IF((C3>0),(

IF((H3<=C3),(

IF(C3=M3,0,H3)),C3)),0)-

IF(F3<=C3, F3, 0)+

IF(C3<0, C3, 0)))

Alteryx's IIF are the same as Excel If's so I just converted it that way

IIF((

IIF([LEDGER OPEN BALANCE]>0,(

IIF([DEBT 300 + DAYS]<=[LEDGER OPEN BALANCE],(

IIF([LEDGER OPEN BALANCE]=[ADJUSTMENT],0,[DEBT 300 + DAYS])),[LEDGER OPEN BALANCE])),0)-

IIF([DEBITS 300 + DAYS JOURNAL INC VAT]<=[LEDGER OPEN BALANCE], [DEBITS 300 + DAYS JOURNAL INC VAT], 0)+

IIF([LEDGER OPEN BALANCE]<0, [LEDGER OPEN BALANCE], 0))<=0, 0, (

IIF(([LEDGER OPEN BALANCE]>0),(

IIF(([DEBT 300 + DAYS]<=[LEDGER OPEN BALANCE]),(

IIF([LEDGER OPEN BALANCE]=[ADJUSTMENT],0,[DEBT 300 + DAYS])),[LEDGER OPEN BALANCE])),0)-

IIF([DEBITS 300 + DAYS JOURNAL INC VAT]<=[LEDGER OPEN BALANCE], [DEBITS 300 + DAYS JOURNAL INC VAT], 0)+

IIF([LEDGER OPEN BALANCE]<0, [LEDGER OPEN BALANCE], 0)))

Workflow0506.yxmd

All comments

Luke_C

Hi @Data_Alter

It would be helpful if you could share some data, but I think this would work. You will need to swap in the column names instead of cells:

C13= M13 then 0

elseif C13.> 0 and H13 <=C13

then

H13

elseif C13.> 0 and H13 > C13

then

C13

else

endif

IraWatt

I would say that translates to:

IF [C13]>0 THEN
IF [H13]<=[C13] THEN
IF [C13]=[M13] THEN 0 
ELSE [H13]
ENDIF
ELSE [C13]
ENDIF
ELSE 0
ENDIF

Definitely double check the output is the same 😄

Any questions or issues please ask
HTH!
Ira

Workflow0506.yxmd

Data_Alter

Thanks the data is here and I need to replicate formula in column I.

Demo Data.xlsx

IraWatt

I added the formula you initially asked for:

Workflow0506.yxmd

Data_Alter

@IraWatt Thank you so much.

I thought using the first part , I will be replicate the whole formula but I am getting stuck. Is it possible to replicate the whole thing in cell I3

IraWatt

@Data_Alter Column I's formula is completely different from the one you initially gave:

IF((

IF((C3>0),(

IF((H3<=C3),(

IF(C3=M3,0,H3)),C3)),0)-

IF(F3<=C3, F3, 0)+

IF(C3<0, C3, 0))<=0, 0, (

IF((C3>0),(

IF((H3<=C3),(

IF(C3=M3,0,H3)),C3)),0)-

IF(F3<=C3, F3, 0)+

IF(C3<0, C3, 0)))

Alteryx's IIF are the same as Excel If's so I just converted it that way

IIF((

IIF([LEDGER OPEN BALANCE]>0,(

IIF([DEBT 300 + DAYS]<=[LEDGER OPEN BALANCE],(

IIF([LEDGER OPEN BALANCE]=[ADJUSTMENT],0,[DEBT 300 + DAYS])),[LEDGER OPEN BALANCE])),0)-

IIF([DEBITS 300 + DAYS JOURNAL INC VAT]<=[LEDGER OPEN BALANCE], [DEBITS 300 + DAYS JOURNAL INC VAT], 0)+

IIF([LEDGER OPEN BALANCE]<0, [LEDGER OPEN BALANCE], 0))<=0, 0, (

IIF(([LEDGER OPEN BALANCE]>0),(

IIF(([DEBT 300 + DAYS]<=[LEDGER OPEN BALANCE]),(

IIF([LEDGER OPEN BALANCE]=[ADJUSTMENT],0,[DEBT 300 + DAYS])),[LEDGER OPEN BALANCE])),0)-

IIF([DEBITS 300 + DAYS JOURNAL INC VAT]<=[LEDGER OPEN BALANCE], [DEBITS 300 + DAYS JOURNAL INC VAT], 0)+

IIF([LEDGER OPEN BALANCE]<0, [LEDGER OPEN BALANCE], 0)))

Workflow0506.yxmd

Data_Alter

Thanks @Ira , you are a star. I didn't know IIF statement is same as excel.

IraWatt

No worries @Data_Alter ! 😄

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703