Calculating a UPC Check Digit

[Da Food Guy]

Hi Everyone - another UPC issue. A standard 12 digit UPC is a combination of 11 digits and a calculated 12th digit. I need to create a formula that I can drag down an existing list that calculates the check digit. Here is the instructions on how to calculate it, but I have no clue how to write the formula for it:

Step One:
Suppose you want to find the Check Digit for the UCC-12 (U.P.C.) Number 61414121022. Set up a table with 12 columns, and put the number 61414121022 into Positions One through Eleven. Position Twelve will be blank because it is reserved for the Check Digit.

Step Two:
Add the numbers in Positions One, Three, Five, Seven, Nine, and Eleven:

(6 + 4 + 4 + 2 + 0 + 2 = 18).

Step Three:
Multiply the result of Step Two by three:

(18 x 3 = 54).

Step Four:
Add the numbers in Positions Two, Four, Six, Eight, and Ten:

(1 + 1 + 1 + 1 + 2 = 6).

Step Five:


Add the results of Step Three and Step Four:

(54 + 6 = 60).

Step Six:
The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 0.

Its a dozy, I know, but I'm not sure how it can be done in a single string.

 

[ammdumas]

I'd do it two steps. In one cell, do parts 1 through 5, which is:
=((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(A2,2,1)+MID(A2,4,1)+MID(A2,6,1)+MID(A2,8,1)+MID(A2,10,1)

That gets you your '60'. Steps two is part 6 in another cell which would be
=RIGHT(B1,1)*10

That gets you your '0'. Sooooo, if column A is the eleven digit number, Column B could be Step 1, Column C could be Step to and column D could be the 12-digit code which would be a simple concatenate formula

=CONCATENATE(A1,D1)

Does that work?

 

[Jay Petrulis]

Straightforward way:

If your data is in A1:K1, in L1 try the following:

=IF(MOD((SUM(A1,C1,E1,G1,I1,K1)*3+SUM(B1,D1,F1,H1,J1)),10)>5,10-MOD((SUM(A1,C1,E1,G1,I1,K1)*3+SUM(B1,D1,F1,H1,J1)),10),MOD((SUM(A1,C1,E1,G1,I1,K1)*3+SUM(B1,D1,F1,H1,J1)),10))

If the 11-digit number is on one cell, try

MOD((SUM(--MID(A1,{1,3,5,7,9,11},1))*3+SUM(--MID(A1,{2,4,6,8,10},1))),10)

=IF(xxx>5,10-xxx,xxx) where xxx is the above formula

EDIT: Shorter, and I did not want to add a new post.

=MIN(MOD((SUM(--MID(A1,{1,3,5,7,9,11},1))*3+SUM(--MID(A1,{2,4,6,8,10},1))),10),10-MOD((SUM(--MID(A1,{1,3,5,7,9,11},1))*3+SUM(--MID(A1,{2,4,6,8,10},1))),10))

 

[ammdumas]

That'll work. I went under the assumption that the only reason you have twelve columns of data IS to figure out this number. Putting the same number into 11 columns seems silly...unless you mean put each number of that UPC number into its own cell. Then I just didn't pay attention.

 

[Da Food Guy]

God I love you guys! Thanks a million! Works like a charm!

 

[Da Food Guy]

Okay, I'm a nitwit. I just tried doing the same forumla on my own to produce a check digit for a GTIN, which is 14 digits. Same rules as above, but you need to add position 13 to the odd mid equation and position 12 to the even mid equation

=IF(MOD((SUM(--MID(B4,{1,3,5,7,9,11,13},1))*3+SUM(--MID(B4,{2,4,6,8,10,12},1))),10)>5,10-MOD((SUM(--MID(B4,{1,3,5,7,9,11,13},1))*3+SUM(--MID(B4,{2,4,6,8,10,12},1))),10),MOD((SUM(--MID(B4,{1,3,5,7,9,11,13},1))*3+SUM(--MID(B4,{2,4,6,8,10,12},1))),10))

I get a 4, but the response should be 6. Any ideas? Here is the rules that I am trying to follow:

Step One:
Suppose you want to find the Check Digit for the EAN/UCC-14 Number 9101454121022. Set up a table with 14 columns, and put the number 9101454121022 into Positions One through Thirteen. Position Fourteen will be blank because it is reserved for the Check Digit.

Step Two:
Add the numbers in Positions One, Three, Five, Seven, Nine, Eleven, and Thirteen:

(9 + 0 + 4 + 4 + 2 + 0 + 2 = 21).

Step Three:
Multiply the result of Step Two by three:

(21 x 3 = 63).

Step Four:
Add the numbers in Positions Two, Four, Six, Eight, Ten, and Twelve:

(1 + 1 + 5 + 1 + 1 + 2 = 11).

Step Five:
Add the results of Step Three and Step Four:

(63 + 11 = 74).

Step Six:
The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 6.

 

[Da Food Guy]

Okay, I'm a nitwit. I just tried doing the same forumla on my own to produce a check digit for a GTIN, which is 14 digits. Same rules as above, but you need to add position 13 to the odd mid equation and position 12 to the even mid equation

=IF(MOD((SUM(--MID(B4,{1,3,5,7,9,11,13},1))*3+SUM(--MID(B4,{2,4,6,8,10,12},1))),10)>5,10-MOD((SUM(--MID(B4,{1,3,5,7,9,11,13},1))*3+SUM(--MID(B4,{2,4,6,8,10,12},1))),10),MOD((SUM(--MID(B4,{1,3,5,7,9,11,13},1))*3+SUM(--MID(B4,{2,4,6,8,10,12},1))),10))

I get a 4, but the response should be 6. Any ideas? Here is the rules that I am trying to follow:

Step One:
Suppose you want to find the Check Digit for the EAN/UCC-14 Number 9101454121022. Set up a table with 14 columns, and put the number 9101454121022 into Positions One through Thirteen. Position Fourteen will be blank because it is reserved for the Check Digit.

Step Two:
Add the numbers in Positions One, Three, Five, Seven, Nine, Eleven, and Thirteen:

(9 + 0 + 4 + 4 + 2 + 0 + 2 = 21).

Step Three:
Multiply the result of Step Two by three:

(21 x 3 = 63).

Step Four:
Add the numbers in Positions Two, Four, Six, Eight, Ten, and Twelve:

(1 + 1 + 5 + 1 + 1 + 2 = 11).

Step Five:
Add the results of Step Three and Step Four:

(63 + 11 = 74).

Step Six:
The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 6.

 

[ammdumas]

Ummm, I'm confused. How do you get 6 from 74?

 

[Da Food Guy]

Duh, sorry - the number I was using the formula on is 9101454121022

 

[ammdumas]

So everything cool?