Calculate Check Digit

[Mogro]

Hi All,

I think this should be fairly easy but I am just a bit of an issue getting a formula to work. I need to calculate a check digit for a bar code.

The # is 3910037276

The instructions to figure check digit are as follows:

Work with the leftmost 10 digits.
Starting from the left take the:

First digit (3) times 1 (= 3)
Second digit (9) times 2 (= 18)
Third digit (1) times 1 (= 1)
Fourth digit (0) times 2 (= 0)
Fifth digit (0) times 1 (= 0)
Sixth digit (3) times 2 (= 6)
Seventh digit (7) times 1 (= 7)
Eight digit (2) times 2 (= 4)
Ninth digit (7) times 1 (= 7)
Tenth digit (6) times 2 (= 12)

Add these together – 3 + 18 + 1 + 0 + 0 + 6 + 7 + 4 + 7 + 12 = 58.

Take the last digit of the total (8) and subtract from 10 which gives a check digit of 2.

I appreciate any help you can provide. I did try to search forums for previous threads but didn't see exactly what I need.

Thank you!
MG

 

[sadboy309]

You click (Ctrl + Shift +Enter) for the end of line formula:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3910037276[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]{=10 - RIGHT(SUM(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)*{1;2;1;2;1;2;1;2;1;2}),1)}[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mogro]

THANK YOU! Will give this a try. I did come up with a solution but not quite as elegant as yours.

I appreciate the help!

 

[FormR]

Hi, welcome to the forum!

Here is normally entered option you can also try.

=10-MOD(SUMPRODUCT(0+MID(LEFT(A1,10),{1,2,3,4,5,6,7,8,9,10},1),{1,2,1,2,1,2,1,2,1,2}),10)

 

[XOR LX]

Another normally entered option:

=10-MOD(SUM(MID(A1,{1,2}+{0;2;4;6;8},1)*{1,2}),10)

Regards

 

[finnestad]

Hmm,

I have a bar code 06378338390 with a check digit of 6

when i put the bar code in with the formulas above, it calculates it as 3

 

[Scott Huish]

I believe the check digit should be 7, not 6, but the reason you are getting 3 is because if you enter the values as numbers, Excel will drop the leading zero. Enter it as text instead (i.e. with an apostrophe before it).