# Calculating UPC Barcode Check Digits

#### mbon91

##### New Member
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.
=right(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)))),1)

This should return just the check digit.

The other formulas didn't accurately calculate when the check digit was equal to "0." They would return "10" instead and have 13 digits total.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### excel_wanabee

##### New Member
Perfect. Thanks!

The formulas in this thread may have had the problem with a 0 check digit. Others around the forum and internet, now that I look back at my testing sheet, seem to have issues with how the final remainder is reached because the check digit seemed to always be off by 1 -- higher or lower than the correct check digit. Hope this "thread extension" helps everyone else googling this solution.

#### Andrew Fergus

##### MrExcel MVP
This is a condensed version that fixes the 10 vs 0 issue:

=MOD(10-MOD((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))), 10),10)

#### fming

##### New Member
This is an old post, but ....

My Check Digit Calculator is based on an 11-Digit UPC-A Code without the check-digit. The prefix is there, we will ADD the Check Digit. Sample image is below. Cell D3 has the 11 digit UPC-A code

=10-(MOD(ROUND((
((MID(D3,1,1)+MID(D3,3,1)+MID(D3,5,1)+MID(D3,7,1)+MID(D3,9,1)+MID(D3,11,1))*3)
+(MID(D3,2,1)+MID(D3,4,1)+MID(D3,6,1)+MID(D3,8,1)+MID(D3,10,1))
)/10,1),1)*10)

This is bascially the following formula:
10 - (MOD((((SUM_ODDS x 3) + (SUM_EVENS)) /10),1) * 10)

It is based on the Steps below

Steps

S1: Sum all ODD digits & multiply by 3.
formula: S1=(MID(D3,1,1)+MID(D3,3,1)+MID(D3,5,1)+MID(D3,7,1)+MID(D3,9,1)+MID(D3,11,1))*3

S2: Sum all EVEN digits.
formula: S2=(MID(D3,2,1)+MID(D3,4,1)+MID(D3,6,1)+MID(D3,8,1)+MID(D3,10,1))

S3: Add results of S1 and S2
formula: S3=S1 + S2

S4: Divide the result of S3 by 10.
formula: S4=ROUND(S3/10,1)

S5: Multiply fraction of S4 (should be tenths) by 10.
formula: S5=MOD(s4,1) * 10

S6: Check Digit is 10 minus S5
formula: s6=10-S5

IMPORTANT Notes:
1. Determine if a digit is in an ODD or EVEN position by counting from the left. Since I only have 11 digits, it 's simple.
2. Rounding is needed because of Excel
3. Any number modulo 1 will return the fractional part. You can also calculate it by X-TRUNC(X)

Last edited:

#### fming

##### New Member
Modified formula
=IFERROR(RIGHT(10-(MOD(ROUND((
((MID(D2,1,1)+MID(D2,3,1)+MID(D2,5,1)+MID(D2,7,1)+MID(D2,9,1)+MID(D2,11,1))*3)
+(MID(D2,2,1)+MID(D2,4,1)+MID(D2,6,1)+MID(D2,8,1)+MID(D2,10,1))
)/10,1),1)*10)),0)

IMPORTANT Notes:
1. Determine if a digit is in an ODD or EVEN position by counting from the left. Since this is only 11 digits, we're the 1st digit is odd.
2. Rounding is needed becase of excel.
3. Any number modulo 1 will return the fractional part. You can also calulate it by X-TRUNC(X)
4. The RIGHT funtion will return 0 - the right most character - if the Check Digit is 10, otherwise it will return the Check Digit.
5. The IFERROR function is used to return 0 if the 11-digit UPC-A code is missing.

#### jlarkin

##### New Member
Hey guys, for the remainder, you should be using the MOD() function and check out what happens when you take the MOD of a negative number.

I came across this elegant solution elsewhere on MrExcel.com:
=MOD(-SUM(MID(A1,{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)

Beautiful!

If your cell contains a number instead of text, try this first TEXT(A1,"000000000000") so it is:
=MOD(-SUM(MID(TEXT(A1,"000000000000"),{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)

#### GR00007

##### Board Regular
How 'bout a formula?

Function UPC_CheckDigit(r As Range) As String
'
' Calculate Check Digit
'
'
Dim i As Integer
Dim TotalOdd As Integer
Dim TotalEven As Integer
Dim Total As Integer
Barcode = Trim(r.Formula)
vEO = Len(Barcode) Mod 2
If vEO = 0 Then
'EVEN - get set 1
For i = 2 To Len(Barcode) Step 2
TotalOdd = TotalOdd + CInt(Mid(Barcode, i, 1))
Next i
TotalOdd = TotalOdd * 3
'EVEN - get set 2
i = 0
For i = 1 To Len(Barcode) Step 2
TotalEven = TotalEven + CInt(Mid(Barcode, i, 1))
Next i
Else
'ODD - get set 1
For i = 1 To Len(Barcode) Step 2
TotalOdd = TotalOdd + CInt(Mid(Barcode, i, 1))
Next i
TotalOdd = TotalOdd * 3
'ODD - get set 2
i = 0
For i = 2 To Len(Barcode) Step 2
TotalEven = TotalEven + CInt(Mid(Barcode, i, 1))
Next i
End If
Total = TotalOdd + TotalEven
UPC_CheckDigit = 10 - IIf(Right(Total, 1) = 0, 10, Right(Total, 1))
End Function