Convert Excel formula to Alteryx

[JeremyZ]

Hi All

How can I convert the below excel formula in Alteryx?

I have put my current formula in column C. See as below:

Formula: =IF(A2="11",IF(B2="","",(IF(MID("10X98765432",MOD(SUMPRODUCT(MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(B2,18,18),"Pass","Error"))),"Not ID")

Thanks in advance!

[binuacs]

@JeremyZ would you be able to provide the logic for the rules other than the excel formula? 

[JeremyZ]

Hi, 

Sorry for late reply.

The logic is:

1. when column A value equals to 11, multiply 2 to the 17th power + 2 to the 16th power + ...... + 2 to the 1st power by the 1-17th digit of the ID number (which is the value of column B), and then sum up.

2. Divide the result by 11 and get the reminder. Use MID function in Excel to return a specific number of digit from "10X98765432", the position we start is (the reminder+1). if this number equals to the last digit of the ID number (which is the value of column B), then "pass, otherwise "Error".

Thanks for your help!

[binuacs]

@JeremyZ thanks for providing the logic, question, when you say 17th power of 2 , do you mean take the 17th digit of the id_no and find the power of 2?

eg: id_no - has total 18 digits

the 17th digit would be 4 and 17th power of 2 is 4^2. Can you confirm?

also confirm what the formula returns MID(B6,ROW(INDIRECT("1:17")),1)? 

[JeremyZ]

Hi binuacs, 

sorry I didn't make it clearly.

For example, for the cell you highlight in yellow, SUMPRODUCT( MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))) means (1st digit of id_no * 17th power of 2) + (2nd digit of id_no * 16th power of 2) + ... + (16nd digit of id_no * 2nd power of 2) + (17th digit of id_no * 1st power of 2), which is (3*2^17+1*2^16+...+7*2^2+4*2^1), and we get the result A.

And then divide A by 11 and get the remainder. Then return a specific number of digit from "10X98765432", the position we start is (the remainder+1), for example, if the remainder is 1, we will return the 2nd number of "10X98765432", which is 0.

If this number equals to the 18th digit of id_no , then pass, otherwise "error". Here for example, as 0 does not equal to 2, the result is "error.

Thanks for your help!

[JeremyZ]

Hi binuacs, 

Sorry I didn't make it clear.

For your question, for example, for the cell you highlighted, SUMPRODUCT(MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17"))) means (1st digit of id_no * 17th power of 2) + (2nd digit of id_no * 16th power of 2) + ... + (16th digit of id_no * 2nd power of 2) + (17th digit of id_no * 1st power of 2), which is (3*2^17+1*2^16+...+7*2^2+4*2^1), and we get the result A.

Then divide A by 11 and we get the remainder. Then return one specific digit from "10X98765432", the position we start is (the reminder+1), for example, if the remainder we get is 1, the position we start is 2, which is 0.

Then if this number equals to the last digit of id_no, then pass, otherwise error. Here as the last digit is 2, the result will be error.

Thanks for your help!

[binuacs · Accepted solution]

@JeremyZ I applied the rules you mentioned in the above post but still not getting the correct answer. Attaching the workflow for your reference, I am thinking something is missing in the logic

Note: I just tried for the value '310110198206012742'

[JeremyZ]

Hi binuacs, 

I add some logics in your workflow and it works, thanks for your help!

[binuacs]

@JeremyZ that is great, I am glad it worked for you!!!