How To Calculate Check Digit (MOD 10)

[ctsuhako]

How do I create a formula that will calculate a check digit for a 9-digit number using MOD 10? The weighting is 212121212. The problem I am having is that if the sum of the product is in double digits, they must be treated as individual digits.
Example:

Number To Be Calculated: 0 0 4 4 5 5 5 2 5

Weighting: 2 1 2 1 2 1 2 1 2

Sum: 0+0+8+4+1+0+5+1+0+2+1+0

22/10 = 2 r2
10-2 = 8 (Check Digit)

Is it possible to create such a formula? I need to calculate several hundred and do not want to do this manually! Thanks for any suggestions!

 

[IML]

I'm not sure I fully got what you were after, but give this a whirl
=10-MOD(SUM(--MID(TEXT($A1,"000000000"),{2,4,6,8},1),--RIGHT(MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),--LEFT(TEXT((MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),"00"))),10)


It is hard coded for a length of 9 and and your weighting.

 

[ctsuhako]

Thanks! It works great except for one thing:

Sometimes the formula gives me a check digit of "10" instead of "0". For example, if the number to be calculated is "004455529" the check digit is "10", not "0". I need to end up with a 10-digit number. Thnks again!

 

[IML]

so ten isn't a valid check digit? All I had to go on was the 10-2=8 in your example. I assumed 10 was a fixed number. If not andall that is wrong is a 10 is showing up when it should be zero, change the formula to:
=right(the formula)+0

 

[Aladin Akyurek]

Using the free morefunc.xll add-in, either as a 2-step or single-step calculation...

Book13
	A	B	C	D
1		Step1	Step2	SingleStep
2	004455525	008410510210	8	8
3
Sheet1

As a 2-step calculation:

B2:

=MCONCAT(MID(A2,{1;2;3;4;5;6;7;8;9},1)*({2;1;2;1;2;1;2;1;2}))

C2:

=10-MOD(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),10)

which should give the desired check digit.

As a 1-step calculation:

D2:

=10-MOD(SUMPRODUCT(--MID(SETV(MCONCAT(MID(A2,{1;2;3;4;5;6;7;8;9},1)*({2;1;2;1;2;1;2;1;2}))),ROW(INDIRECT("1:"&LEN(GETV()))),1)),10)

 

[ctsuhako]

Thnaks again, IML. Works like a charm. I'm sorry I wasn't a little clearer on what I wanted to accomplish, but this is new to me. I am going to try to decipher the formula piece by piece; it is the only way for me to learn what you did. Thanks!

 

[rrdonutz]

Here's another formula that's slightly shorter:

=MOD(10-(SUMPRODUCT(--MID(A1,{1;2;3;4;5;6;7;8;9},1)*({2;1;2;1;2;1;2;1;2}))-9*SUMPRODUCT(--(MID(A1,{1;3;5;7;9},1)*1>4))),10)

This portion of the formula:

=SUMPRODUCT(--MID(A1,{1;2;3;4;5;6;7;8;9},1)*({2;1;2;1;2;1;2;1;2}))

is fairly straightforward. This part of the formula:

SUMPRODUCT(--(MID(A1,{1;3;5;7;9},1)*1>4)))

tests to see how many of the 1st, 3rd, 5th, 7th, and 9th digits are equal to 5 or more. If a digit is equal to 5, then its product (with 2)=10, and the sum of these 2 digits is 1 (1+0), which is the same as 10-9=1. Similarly for 6, 6*2=12, and then 1+2=3, which is the same as 12-9=3. And so on for 7*2=14, 1+4=5, which is the same as 14-9=5. Therefore, multiplying the number of odd digits greater than 4, by -9, will do the proper substraction from the first SUMPRODUCT() value.

--Tom

MrExcel81903.xls
	A	B	C	D
1	004455525		8
2	066415522		3
3	632632137		6
4	903107621		0
Sheet4

 

[IML]

Still noodling with this one: Also
=RIGHT(10-MOD(SUM(--MID(TEXT((MID(A1,{1;2;3;4;5;6;7;8;9},1)*({2;1;2;1;2;1;2;1;2})),"00"),{1,2},1)),10))+0

 

[rrdonutz]

Noodling, part 2:

=MOD(10-(SUM(CEILING(MOD(MID(A1,{1,2,3,4,5,6,7,8,9},1)*{2,1,2,1,2,1,2,1,2},9.5),1))),10)

Pero, no más.

--Tomás

 

[IML]

Pero, no más.

Por que? Muy bien, Sr. Thomas.