GTIN Barcode Check Digit Help

Hi Everyone,

My company is in the process of creating UPC barcodes for our product lines. We use the GTIN-12 format - eleven digits plus the calculated twelfth check digit. Right now, we're entering the 11 digits into an online check digit calculator to get that last number; the problem is, we have about 40,000 items to go and none of us want to type all of that into a calculator.

This is the formula:
N1 through N11 signify the digits in the code
(N1*3)+(N2*1)+(N3*3)+(N4*1)+(N5*3)+(N6*1)+(N7*3)+(N8*1)+(N9*3)+(N10*1)+(N11*3)
Take the sum of this and subtract it from the nearest equal or higher multiple of 10.

Example:
74702813169
(7*3)+(4*1)+(7*3)+(0*1)+(2*3)+(8*1)+(1*3)+(3*1)+(1*3)+(6*1)+(9*3)=102
110-102=8
Complete barcode: 747028131698

Is it possible to calculate this in Excel? The problem I'm finding is identifying what to subtract the sum from, the nearest equal or higher multiple of 10. There must be a way, it's just that none of us know how.

I hope I've explained this clearly enough. Can anyone help us? It would be greatly appreciated!

Brianna

The check digit is

=10 - MOD( SUMPRODUCT(MID(A1, {1,2,3,4,5,6,7,8,9,10,11}, 1) * {3,1,3,1,3,1,3,1,3,1,3}), 10)

... or the complete number is

=A1 & 10 - MOD( SUMPRODUCT(MID(A1, {1,2,3,4,5,6,7,8,9,10,11}, 1) * {3,1,3,1,3,1,3,1,3,1,3}), 10)

Thank you so much for the incredibly fast reply!

It was very helpful, but I've run into a small problem. It's not quite working for numbers whose check digits are 0 - in other words, for numbers where the sum of multiplications is a multiple of 10. Instead of giving me a 0, it's giving me a 10. Since I have no idea what most of your formula means, I don't know how to fix it. Do you?

Thank you again,
Brianna

Maybe

=A1 & MOD(10 - MOD( SUMPRODUCT(MID(A1, {1,2,3,4,5,6,7,8,9,10,11}, 1) * {3,1,3,1,3,1,3,1,3,1,3}), 10), 10)

That was perfect! Thank you so much, this is a great help.

Since I have no idea what most of your formula means

See Help for SUMPRODUCT and figure it out, or ask questions.

This is a 'teaching to fish' forum, not a fish store.

Hello! I'm in the same situation, but for some reason I can't get it to work. I have a few thousand numbers to do, here is one of them 09233401123. Could you please show me the formula using that number?