UPC check digit

E

Eileen

Can someone help to write me an excel formular to
calculate the check digit of UPC code?

Thanks !
 
B

Bernard Liengme

HI Eileen,
I do hope this is not homework!

A Google found the site
http://www.barcode-graphics.com/support_desk/check.htm
which gave this algorithm
The check digit for each UPC is calculated using a five-step algorithm based
on the UPC's first eleven digits. For a sample UPC--0 12345 67890--the check
digit is calculated as follows.

.. Step 1
Starting at the left, add all characters in the odd positions, (first from
the left, third from the left, etc.) starting with the number system
character. In the example: 0+2+4+6+8+0 = 20.

.. Step 2
Multiply the sum obtained in Step 1 by 3. In the example: 20 x 3 = 60.

.. Step 3
Starting from the left, sum all the characters in the even positions (second
from the left, fourth from the left, etc.). In the example: 1 + 3 + 5 + 7 +
9 = 25.

.. Step 4
Add the product of Step 2 to the sum of Step 3. In the example: 60 + 25 =
85.

.. Step 5
The check character is the smallest number in which, when added to the sum
of Step 4, produces a multiple of 10. Therefore, the check character of the
sample UPC is 5. (85 + 5 = 90, a multiple of 10

I will show first how to check that a UPC code is valid

We will need to enter the UPC code as text because of the leading zero. So I
entered '073168879504 in B4
For steps 1 and 2 combined, in B6 I used
=SUMPRODUCT(--(MID(B4,{1,3,5,7,9,11},1)))*3
For step 3 I used in B7
=SUMPRODUCT(--(MID(B4,{2,4,6,8,10},1)))
For step 4, in B8 use =B6+B7
The check digit is found in B9 with
=IF(MOD(B8,10)=0,0,10-MOD(B8,10))
alternatively =MOD(10-MOD(B8,10),10)
If the code valid? =IF(VALUE(RIGHT(B4))=B9,"OK","error")

Now if you enter just the first 11 digits ('07316887950) in B4
this method will tell you the correct 12th digit.

Of course, steps 1 thru 5 can be combined into one formula
=MOD(10-MOD(SUMPRODUCT(--(MID(B4,{1,3,5,7,9,11},1)))*3+SUMPRODUCT(--(MID(B4,
{2,4,6,8,10},1))),10),10)

Best wishes
 
B

BenjieLop

Bernard,

In the formulas that you have,

<< For steps 1 and 2 combined, in B6 I used
=SUMPRODUCT(--(MID(B4,{1,3,5,7,9,11},1)))*3

For step 3 I used in B7
=SUMPRODUCT(--(MID(B4,{2,4,6,8,10},1))) >>

there is an extra parenthesis at the end of each. This is the onl
minor correction that needs to be made. Otherwise, everything els
works perfectly
 
Top