Calculating UPC Barcode Check Digits

ktothek

New Member
Joined
Mar 20, 2008
Messages
4
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.
calc-upc-diagram1.gif

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
 
Last edited:
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
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
=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)))
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
=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))))

<tbody>
</tbody>

This will calculate correctly. Other suggestions at best would produce almost correct solutions.
 
Upvote 0
=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))))

<tbody>
</tbody>

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?
 
Upvote 0
Under what circumstances do the other solutions not work? Or what renders them "almost correct"?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top