Excel IFERROR expression help when creating a formula expression in Alteryx

[Iamironman]

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.

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.

[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])

ENDIF

However I am not arriving to the correct value.

[Yoshiro_Fujimori · Accepted solution]

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.