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]

Welcome to the board.

There's probably a way of doing this in a sheet formula (although I can't see it being simple) but if not, how are you with a VB solution?

 

[Weaver]

If you're ok with vb, this appears to work

Function modulo(ByVal num As Integer, weight As Integer)
    n = Format(num, "0000")
    w = Format(weight, "0000")
    o = 0
    For i = 1 To 4
        o = o + Mid(n, i, 1) * Mid(w, i, 1)
    Next i
    c = 11 - o Mod 11
    modulo = num & c
End Function

Put the code in a standard module, then if A1 holds the number and B1 holds the 'weight', formula in C1:

=modulo(A1,B1)

gives the result

HTH

 

[thesleeperr]

Thanks for the welcome. Unfortunately, I do not know how to use VB. I was looking for more of an array formula.

 

[Weaver]

Have a look at the links provided in my signature below.

Having said that, there's probably an array formula expert working on this one as we speak.

 

[Weaver]

Actually, you don't need an array formula.

This uses sumproduct

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

Just shows what you can come up with using a little lateral thinking.

HTH

 

[thesleeperr]

Yes, I did look at your links. I've been trying to create a formula that works for my particular situation by looking at what other people have posted and trying to modify it, but have had no luck.

 

[Weaver]

Yes, I did look at your links. I've been trying to create a formula that works for my particular situation by looking at what other people have posted and trying to modify it, but have had no luck.

We might have cross posted, but check out my response in #6 as I think this might be a result.

 

[thesleeperr]

WOW!!! That worked. Thank you very much!

 

[Weaver]

Yeah, I surprised myself with that one!