Workflow Help: IF / AND / OR Excel Formula to Alteryx

** Corrections / Updates Made**

Hello Y'all, first time post! I'm looking for some help translating this formula from an excel file into Alteryx. The Alteryx formula below works, but produces higher numbers of records than what the formula does in excel. Calling for a consult on this one as I feel my syntax is wrong somewhere, or just be blunt and tell me I'm doing it wrong.

Excel Formula:

=IF((L10="x")*AND(OR(AC10="y",AC10="a"),OR(BD10="y",BD10="a"),(BG10=0)),"VALID","")

If L10 (Certification) is checked AND AC OR BD have a value (They took that e-learning course), BUT They have not had the in-person course of BG10, then they are VALID to take BG10.

My confusion is around the * in front of the AND statement making it a wild card. Could that be the issue, and possibly the original formula from the user be wrong? I asked him to explain the logic, and he stated to me that his original formula threw an error, and he hit "ok" to let excel fix it.

My Alteryx attempt:

IF (

[L10] == "X"

AND (

([AC10] == "Y" OR [AC10] == "A")
OR ("[BD10] == "Y" OR [BD10] == "A")
OR [BG10] == Null()

)
)

THEN "VALID"
ELSE Null()
ENDIF

Workflow

Accepted answers

Komarev

I figured it out finally after working on it all day... It looks like the OR statements in Excel behave differently than that of Alteryx.

Wrong attempt:

IF (

[L10] == "X"

AND (

([AC10] == "Y" OR [AC10] == "A")
OR ("[BD10] == "Y" OR [BD10] == "A")
OR [BG10] == Null()

)
)

THEN "VALID"
ELSE Null()
ENDIF

Correct Attempt:

IF (

[L10] == "X"

AND (

([AC10] == "Y" OR [AC10] == "A")
AND ("[BD10] == "Y" OR [BD10] == "A")

AND [BG10] == Null()
)

)

THEN "VALID"
ELSE Null()
ENDIF

All comments

Claje

I'm going to try and translate your formula into English. Can you

Roughly, If the current value in column L = "X" AND one of the following is true:
The value of AC is Y or A,

The value of BD is Y or AB is A

The value of BG is 0

Then output Valid, otherwise output an empty value.

If so, I think this should work.

Roughly, the only question I have is, are either Alteryx or Excel case-sensitive in these formulas? Do you have a mixed case data source?

The only change I see between your formulas is the case of the letters involved.

Komarev

Apologies, in the original excel formula I changed the names of the columns but missed one:

BD10="y",AB10="a"

should have been:

BD10="y",BD10="a"

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703