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!

 

[PaddyD]

noodle, noodle...

never did bother to check whether this does what it's meant to, but, using morefunc, a construction not massively dissimilar to the following could be used to do a mod10 for arbitrary length strings:

=MOD(10-MOD(SUM((MID(A1,IF(MOD(LEN(A1),2)<>0,1,0)+INTVECTOR(ROUNDDOWN(LEN(A1)/2,0),1,,2),1)+0)*2),10),10)

...array entered.

 

[rrdonutz]

Okay, one last post for anyone remotely interested. In response to PaddyD's MOREFUNC general case formula, the following "out-of-the box" Excel formula will handle any number from 1 to 15 digits in length, or string representation of a number from 1 to 15 digits or more:

=MOD(10-SUMPRODUCT(CEILING(MOD(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)*(1+MOD(ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),2)),9.5),1)),10)

For those less "squeamish", a shorter CSE (array) formula that also uses the INDIRECT() function is:

=MOD(10-SUM(CEILING(MOD(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(1+MOD(ROW(INDIRECT("1:"&LEN(A1))),2)),9.5),1)),10)

Both formulas use this progression of multiplication on successive digits:

2, 2-1, 2-1-2, 2-1-2-1, 2-1-2-1-2, etc.

Note to PaddyD: I couldn't get this formula--
=MOD(10-MOD(SUM((MID(A1,IF(MOD(LEN(A1),2)<>0,1,0)+INTVECTOR(ROUNDDOWN(LEN(A1)/2,0),1,,2),1)+0)*2),10),10)
to work beyond the first example posted below.

--Tom

MrExcel819031.xls
	A	B	C	D
1	004455525	8	8
2	066415522	3	3
3	632632137	6	6
4	903107621	0	0
5	432176343	7	7
6	4	2	2
7	32	2	2
8	637	9	9
9	678195	8	8
10	7080333	3	3
11	82103322	4	4
12	010323497145	3	3
13	127758693850997	5	5
CkDigMod10

 

[PaddyD]

"Note to PaddyD"

THanks for tte heads up, Tom. Like I said, I neveer did test it - now I know I need to. Will get round to it eventaully, no doubt.

 

[jimmy12345]

I have searched but fail to find anything. I am trying to make a mod 10 check digit layout that can calculate the check digit for 1,2,1,2,1..., 2,1,2,1,2,1.....,
1,3,7,1,3,7...., 7,3,1,7,3,1..., up to lets say 60 digits? Is this even possible for excel?

2 Examples:

Substitute P=7

P 0 0 0 1 2 3 4 5 1 0 0 1 1 0 0 0 0
7 0 0 0 1 2 3 4 5 1 0 0 1 1 0 0 0 0
7 3 1 7 3 1 7 3 1 7 3 1 7 3 1 7 3 1 <----- Weight 7,3,1
49 0 0 0 3 2 21 12 5 7 0 0 7 3 0 0 0 0 <---- Product
13 0 0 0 3 2 3 3 5 7 0 0 7 3 0 0 0 0 <----- it drops the 1 on 13
3 0 0 0 3 2 3 3 5 7 0 0 7 3 0 0 0 0
40-36= 4 Check Digit

---------------------------------------------------------------------
P 0 0 0 1 2 3 4 5 1 0 0 1 1 0 0 0 0
7 0 0 0 1 2 3 4 5 1 0 0 1 1 0 0 0 0
1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 <----- Weight 1,2
7 0 0 0 1 4 3 8 5 2 0 0 1 2 0 0 0 0 <---- Product
40-33= 7 Check Digit


<table x:str="" style="border-collapse: collapse; width: 182px; height: 19px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt; width: 208pt;" height="17" width="277">
</td> </tr></tbody></table>This formula comes up with a check digit of 6 when it should be 7

=MOD(10-SUMPRODUCT(CEILING(MOD(MID(A6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A6))),1)*(1+MOD(ROW(INDEX(A:A,1):INDEX(A:A,LEN(A6))),2)),9.5),1)),10)

How would I modify this formula for 1,3,7 weight.

Jim

 

[barry houdini]

Hello Jim, welcome to MrExcel

What are the rules for getting the digits? When you have 7*7=49 you add the digits to get 13 then drop the 1, I assume you always drop 1, does that apply for both 1,2,1,2,1,2 sequences and 7,3,1,7,3,1? For example if you have 2*7=14 do you add the digits to get 5 or just drop the 1 for 4?

for 1,2,1,2,1,2,1 pattern then the former would be

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

and the latter.....

=MOD(SUMPRODUCT(-MOD(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A6)))+1,2)+1),10)),10)

assumes that the "P" has already been replaced so A6 contains digits only.

 

[Eliezer]

here is what i did, i placed the first 11 numbers in col a1 and in b1 i wrote =A1&RIGHT(10-MOD((MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1))*3+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1),10),1)

works wonderfull

 

[Rajesh Muthukumar]

"It is hard coded for a length of 9" -- can this be altered for a length of 15 ?
more like Luhn calculation for card number generation.