MOD 10 calculting

[ROKYD]

Dear all,

I have that formula on excell to calculate the MOD 10 value for a DIGIT CHECK.

BELOW THE FORMULA.
[FONT=&quot]<o></o>[/FONT]
[FONT=&quot]=10-MOD(SUMPRODUCT(--MID(A1,{1,3,5,7,9,11,13,15,17},1))+<o></o>[/FONT]
[FONT=&quot]SUMPRODUCT(--MID(A1,{2,4,6,8,10,12,14,16,18},1)*3),10)[/FONT]

It works for a number but it is not working for the other.
Please; help me.

example datas : 5290000040000850120 /// THE RIGHT NUMBER IS : 2
it is ok for that number:

the other number :

5290000040000850044 // the right number : 4 after the 44

 

[barry houdini]

Hello ROYKD,

Assuming that you are using the method described here then this formula should work for you (it works for any number of digits):

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)

It seems to work for the examples you gave but I would advise you to check that a) it works for a large test set of sample numbers and b) the description in the link matches what you expect

 

[ROKYD]

Hi Norie,

Thank you for reacting, Yes I should precise you the context of all those things.

I need to check the merchant card ID 580120 In the example datas that I sent.
those informations must be printed in the track2 of a merchant card as below

* 925000004000580120c=xxxxxxxxxxxxxxxxxxxxxx
* c = is the DIGIT CHECK VALUE THAT MUST BE OBTAINED by using - Modulo-10(for all the datas at the beginning)
* 925000004000 are constant , so the same for all cards
* 580120 is the merchant card ID

I hope that you catch me now.

 

[ROKYD]

what do you mean by a and b?

 

[barry houdini]

what do you mean by a and b?

I used this formula

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)

and it worked for the 3 examples you gave.

What I meant by a and b was that you should test some more examples and/or look at the method suggested in the link I posted to ensure that the formula does what you want (or not)

 

[ROKYD]

Ok
i will test it and inform you soon

 

[ROKYD]

When i test it, at the place of result i read the following :

#n/a

what does it mean please?

 

[ROKYD]

When i experience that formaul, at the place of result I read the following :

#N/A

what does it mean please?

 

[barry houdini]

It means there's a problem with either the data or the formula.

From the information on your original thread it seems that you are translating the functions and changing the syntax.....If you change the commas , to semicolons ; then that's fine but don't change the part in red

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)

leave that as {1,2} with a comma

If that doesn't work then can you post the exact formula you are using?

 

[ROKYD]

OK it is ok, I HAVE A RESULT BUT, IT IS NOT THE BEST RESULT :
for 925000004000580120? I HAVE 9 INSTEAD OF 2

THISIS MY FORMULA IS FRENCH

=MOD(SOMMEPROD(-STXT(TEXTE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*(MOD(LIGNE(INDIRECT("1:"&NBCAR(A1)))+NBCAR(A1)+1;2)+1);"00");{1,2};1));10)

KINDEST

 

[barry houdini]

Well I don't understand that.....when I try that with the English version I get 2.....can you try that again?

If you still get 9 can you try this:

With the value

925000004000580120

still in A1

Select the cell with the formula, press F2 then select just the red part of the formula in the formula bar....

=MOD(SOMMEPROD(-STXT(TEXTE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*(MOD(LIGNE(INDIRECT("1:"&NBCAR(A1)))+NBCAR(A1)+1;2)+1);"00");{1,2};1));10)

......and now press F9

The highlighted part should change to the calculated values, something like this

{"09";"04";"05";"00";"00";"00";"00";"00";"04";"00";"00";"00";"05";"16";"00";"02";"02";"00"}

Can you paste the result here?