Help: Modulus 11 Prime Method

[thesleeperr]

I am new to this check digit thing, and cannot figure out how to create a formula to find the check digit. I've been looking through this forum and just don't quite understand it.

I need to generate a check digit to be appended to a four digit number. The number has to be entered in a single 4 digit number, and not a 4 individual number. For example:

(1) Four digit number: 6956 (or could even start with one or more zeros)
(2) Weight: 2357
(3)Example:
6 x 2 = 12
9 x 3 = 27
5 x 5 = 25
6 x 7 = 42 Total = 106 ----> 106/11 = 9 with remainder 7
(5) 11 -7 = 4 <--- check digit
(4) And some how add on the 4 check digit to 6956 to get 69564 together in one other cell

 

[Weaver]

PS probably ought to be

=TEXT(A1,"0000")&11-MOD(SUMPRODUCT(--MID(TEXT(A1,"0000"),ROW($A$1:$A$4),1),--MID(B1,ROW($A$1:$A$4),1)),11)

to properly account for if the first number is short of a digit or 2.

 

[barry houdini]

But what if the total (after weighting) is divisible by 11 (or with a remainder of 1) like 9335 or 8077, that formula will give 11 or 10, presumably there should only be a single check digit? (0 if divisble by 11 but what if it's 100?)

 

[Weaver]

Good point.

Do you think it's possible to select a 'weighting' value that would prevent this outcome? It's the first time I've come across this type of checksum.

In any case, I can take some comfort from the fact that my solution at least fits the OPs original description!

 

[barry houdini]

I know that in some cases with mod 11 if the checksum is 10 that that would become "x" so you could do that with this modified version

=TEXT(A1,"0000")&SUBSTITUTE(MOD(SUMPRODUCT(-MID(TEXT(A1,"0000"),ROW($A$1:$A$4),1),--MID(B1,ROW($A$1:$A$4),1)),11),10,"x")