Convert Excel formula to Alteryx

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")

Screen Shot 2023-02-08 at 11.50.10.png

Thanks in advance!

Alteryx Formula.xlsx

Workflow

Developer

Common Use Cases

Transformation

Help

Accepted answers

binu_acs

@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'

Formula_100223.yxmd

All comments

binu_acs

@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 , 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 , then "pass, otherwise "Error".

Thanks for your help!

binu_acs

@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!

binu_acs

@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'

Formula_100223.yxmd

JeremyZ

Hi binuacs,

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

binu_acs

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

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157