anybody understand UPC codes???

Posted by Kevin on November 26, 2001 1:58 PM

I need to set something up in excel to compute the checksum for a UPC code. A UPC code is 11 digits long with a twelfth digit that is the checksum. Here is how a checksum is calculated:

1. add up the 1st,3rd,5th,7th,9th,and 11th digits of the code.

2. multiply the result of step 1 by 3.

3. add up the 2nd,4th,6th,8th, and 10th digits of the code and add this to the product calculated in step 2.

4. the checksum is the SMALLEST number which, when added to the result of step 3, yields a number that is a multiple of ten.

OK, so there you have it. I have figured out a way to pull the digits from any given UPC code and add or multiply as directed above to achieve the desired result. Now I am stuck, because I cannot figure out a way for Excel to figure what number needs to be added to the figure computed above to achieve a number that is a multiple of ten.

Anyone out there know how I can do this?

Thanks,
Kevin

Posted by Aladin Akyurek on November 26, 2001 2:47 PM

Kevin --

Not being familiar with UPC codes, What is the 12th digit (the checksum digit) for

12534876575

Aladin

========

Posted by Russell Hauf on November 26, 2001 3:30 PM

If you've gotten steps 1-3 down, just apply the following formula to the result to get step 4(assuming the result of steps 1-3 is in cell C3):

=CEILING(J14,10)-J14

If you need help with steps 1-3, let me know.

Hope this helps,

Russell

Posted by Aladin Akyurek on November 26, 2001 3:46 PM

OK, an all steps "guess" solution...

Here is what I got:

{12534876575,107,3,125348765753;
12321345687,86,4,123213456874;
98076458098,120,0,980764580980;
63938200039,107,3,639382000393}

I have 11-digit numbers in A2:A4.

In B2 enter: =SUMPRODUCT(MID(A2,{1,3,5,7,9,11},1)+0)*3+SUMPRODUCT(MID(A2,{2,4,6,8,10},1)+0)

and copy down. This executes the 3 steps in one go.

In C2 enter: =IF(MOD(B2,10)=0,0,10-MOD(B2,10))

and copy down. The generates the checksum digit.

In D2 enter: =(A2&C2)+0

and copy down. This generates the Universal Product Code.

You can combine all of this into a big formula if you like.

Aladin

==========