You click (Ctrl + Shift +Enter) for the end of line formula:
A 1 3910037276 2 {=10 - RIGHT(SUM(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)*{1;2;1;2;1;2;1;2;1;2}),1)}
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
You click (Ctrl + Shift +Enter) for the end of line formula:
A 1 3910037276 2 {=10 - RIGHT(SUM(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)*{1;2;1;2;1;2;1;2;1;2}),1)}
Last edited by sadboy309; Apr 26th, 2019 at 09:09 AM.
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!
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)
Last edited by FormR; Apr 26th, 2019 at 09:42 AM.
[code]your code[/code]
Another normally entered option:
=10-MOD(SUM(MID(A1,{1,2}+{0;2;4;6;8},1)*{1,2}),10)
Regards
Advanced Excel Techniques: http://excelxor.com/
Like this thread? Share it with others