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.

 

[Da Food Guy]

No, the response is a 4, and it should be a 6. I modified the formula to include the 12th & 13th per the directions, but it doesn't return the right value

 

[Da Food Guy]

no, I'm still not getting the right result - the check digit for 9101454121022 should be 6, but the formula is returning a value of 4. Any clues?

 

[Domenic]

Can you confirm the 'Check Digit' for the following results?

70

73

75

79

80

 

[Krishnakumar]

Try,

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

HTH

 

[Rogie]

This is my first time to see a UPC number or any other number that uses the same rule. Could you clarify for me the last rule - "the smallest number to round the result of Step 5 to a multiple of 10"? Example: if the result is 76, is the check digit 4 so that the result would be 80 (a multiple of 10)? If the intemediate result is 74, is it 6 so that it is 80? or 4, to be subtracted from 74, for it to be 70?

Thanks

 

[Da Food Guy]

As much as I would like to explain the rule, the multiple of 10 portion has me perplexed as well. However, Krishnakumar's formula seemed to fix the issue, and I adapted it for the 12 digit UPC as well as the 14 digit GTIN. If you want to see the online calculator I copied the instructions from, you can go here:

http://www.uc-council.org/ean_ucc_system/education_support/cdc.html

 

[Jay Petrulis]

Sorry, I did not see the round *UP* to the nearest multiple of 10, only round to the nearest multiple of 10.

The IF formula then can be removed. Use

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

What this is doing is:

1. The intermediate calculation
2. Finding the remainder when integer dividing by 10 (1st MOD)
3. 10 - the remainder to get the adjustment
4. Correcting for the times when the remainder is 0, so as not to result in an adjustment of 10.

For a general solution,

1. Select any cell in row 1
2. Define the following name (Insert>Name...)

Name: arrPOS
RefersTo: =ROW(INDIRECT("1:"&LEN($A1)))

3. Define the following name

Name: CheckDigit
RefersTo:
=MOD(10-MOD((SUM((MOD(arrPos,2)=1)*(--MID(A1,arrPos,1)))*3+SUM(((MOD(arrPos,2)=0))*(--MID(A1,arrPos,1)))),10),10)

Then, all you will need to do is type
=CheckDigit

and the result will appear. This does not require that you pre-specify the length of the string/number.

 

[Eliezer]

the shortest way to calculate a UPC check digit is most probably as the following:
1) put the first 11 Digits into col A1.
2) in col B1 put in =A1&RIGHT(10-MOD(SUM(3*MID(A1,{1,3,5,7,9,11},1))+SUM(1*MID(A1,{2,4,6,8,10},1)),10),1)

Enjoy