Excel IFERROR expression help when creating a formula expression in Alteryx

Hello

I am in the process of automating an excel process, I was gifted this excel expression and for the life of me I can not yield the same result. Please help.

=IF(G20>IFERROR((E20+(B20*(D20/C20)*F20)),0),IFERROR((E20+(B20*(D20/C20)*F20)),0),G20)

This is my current first pass.

Formula_Help.yxmd.png

Attaching a copy of the workflow of the piece I am trying to solve. I am open to any and all suggestions.

Thank you in advance.

Formula_Help.yxmd

Workflow

Expression

Accepted answers

Yoshiro_Fujimori

Hi @Iamironman

To simplify the expression, I would use the column index (B, C, ..., G).

As @OTrieger mentioned, the original expression aims to avoid #DIV/0! error.

The error occurs only when [C] = 0.

So you can simplify the IF clause as below;

Formula

ifError =

IF [C] = 0
THEN 0
ELSE [E] + [B] * ([D] / [C]) * [F]
ENDIF

H =

IF [G] > [ifError]
THEN [ifError]
ELSE [G]
ENDIF

Output

B	C	D	E	F	G	ifError	H
0	0	0	0	36.82783378	0.0007507	0	0
0	3	637.32	637.32	1	195	637.32	195

I hope this helps.

Formula_Help.yxmd

All comments

OTrieger

Hello @Iamironman

The 2 IFERROR formulas in the excel are there to ensure that if all the values are zeros in these cells to get there, as you cannot divide zero by zero it will give error in excel. So this formula is to ensure that you will have a number or a zero. So that is the logic that you need to create in Alteryx, I would use a simple check if ABS(B)+ABS(C)+ABS(D) = o then zero else the calculated value. I'm using ABS - absolute just to ensure that there will be values that might add up to zero, so checking their positive sum. I hope that this will help you.

Iamironman

Thank you for the tip, I would need to see it written out. Trying to follow the logic thru.

Iamironman

I attempted this expression

IF ABS([_3_Month_Usage])+ABS([EO_On_Hand_Qty])+ABS([EO_On_Hand_Cost-PE_USD])=0 then 0
else [EO_On_Hand_Cost-PE_USD]+([_3_Month_Usage]+([EO_On_Hand_Value]/[EO_On_Hand_Qty])*[Exchange Rate])