Calculating UPC Barcode Check Digits

[ktothek]

Hello everyone,

I am new at the forum, and have a tricky question.

My company uses UPC codes for identifying its products. As you might know, the UPC codes have 11 digits + 1 digit check code. And the formulation to calculate this check digit is as following:

Step One: From the right to the left, start with odd position, assign the odd/even position to each digit.

Step Two: Sum all digits in odd position and multiply the result by 3. (7+6+1+1+5+2)*3=66
Step Three: Sum all digits in even position. (2+4+2+7+4)=19
Step Four: Sum the results of step three and four: 66+19=85
Step Five: Divide the result of step four by 10. The check digit is the number which adds the remainder to 10. In our case, divide 85 by 10 we get the remainder 5. The check digit then is the result of 10-5=5.



So, what I did to calculate this was like that:
1. Cell A1 is the eleven digit number: 72641217542
2. Cells A2 to A12 are 7, 2, 6, 4, 1, 2, 1, 7 ,5, 4, 2 correspondingly.
3. Cell A13 has the following formula to calculate the check digit:

=RIGHT(ROUNDDOWN((10-((((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10)-ROUNDDOWN((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10,0))*10)),0),1)


4. Cell A14 has the following formula to combine whole:

=CONCATENATE(A1,A13)


I know it is complicated but it works perfectly. Could you guys advice me any other shorter ways to do that?

Thanks in advance,

Ray

 

[facethegod]

Using the MID approach....

UPC in A1, formula in B1:

=A1&(10-MOD(SUM(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)=0,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,3*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),10))

Confirm with Control+Shift+Enter



HTH

 

[stapuff]

=concatenate(a1,10-right(rounddown((sum(right(a1,1)+mid(a1,9,1)+mid(a1,7,1)+mid(a1,5,1)+mid(a1,3,1)+left(a1,1))*3+sum(mid(a1,10,1)+mid(a1,8,1)+mid(a1,6,1)+mid(a1,4,1)+mid(a1,2,1))),1)))

 

[Andrew Fergus]

Why are you using the rounddown? The mod function gives the remainder which is what you want. If there is a floating point error then you may get a different result.

Andrew

 

[stapuff]

Andrew - I never used MOD. Never had to. Will look into it though.

Kurt

 

[stapuff]

Can you use MOD with the example above as a single cell?

 

[Andrew Fergus]

Using your example, how about something like this:

=B1 & 10-MOD((SUM(RIGHT(B1,1) + MID(B1,9,1) + MID(B1,7,1) + MID(B1,5,1) + MID(B1,3,1) + LEFT(B1,1))*3 + SUM(MID(B1,10,1) + MID(B1,8,1) + MID(B1,6,1) + MID(B1,4,1) + MID(B1, 2, 1))), 10)

Andrew

 

[mbon91]

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IF(RIGHT(CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))),2)="10",LEFT(CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))),11)&"0",CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))))[/TD]
[/TR]
</tbody>[/TABLE]

This will calculate correctly. Other suggestions at best would produce almost correct solutions.

 

[excel_wanabee]

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IF(RIGHT(CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))),2)="10",LEFT(CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))),11)&"0",CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))))[/TD]
[/TR]
</tbody>[/TABLE]

This will calculate correctly. Other suggestions at best would produce almost correct solutions.

I know it's bad form to reopen a dead thread, BUT....mbon91 is right.

I've found a lot of supposed solutions for a check digit calculator in this forum. I think I've tried them all. None of them except the one above calculates the correct solution every single time. I have not found an instance when the above formula's check digit doesn't exactly match the one calculated at GS1 (the authority) Check digit calculator | GS1.

Now to my question, this formula is WAY beyond my ability to even barely comprehend it. This formula provides the complete 12-digit UPC. How can I modify this formula to return only the check digit instead of putting it all together?

 

[Andrew Fergus]

Under what circumstances do the other solutions not work? Or what renders them "almost correct"?

 

[excel_wanabee]

Under what circumstances do the other solutions not work? Or what renders them "almost correct"?

No idea. I can't understand the formulas let alone the math logic within them. I just tried each of the formulas I found here and around the internet against a list of 100 known correct/complete UPCs that I removed the check digit from. I used the GS1 calculator to confirm which was correct when there was a conflict. The above formula was correct 100% of the time.