View Question
Q: Need an excel formula to create a check digit for GTIN-12 - see example in link ( Answered ,   3 Comments )
 Question
 Subject: Need an excel formula to create a check digit for GTIN-12 - see example in link Category: Business and Money Asked by: captcliff-ga List Price: \$10.00 Posted: 13 Oct 2006 09:02 PDT Expires: 12 Nov 2006 08:02 PST Question ID: 773211
 `http://www.uc-council.org/ean_ucc_system/education_support/cdc.html`
 Subject: Re: Need an excel formula to create a check digit for GTIN-12 - see example in link Answered By: maniac-ga on 13 Oct 2006 18:15 PDT Rated:
 ```Hello Captcliff, Hmm. I tried the comment from reinedd, but it did not work on my system. Let's work out a few different ways to solve this. [1] If you can use five cells - you can reproduce the steps described at the web site you referenced in the following way: - A1 has your 11 digit value (step 1) - B1 has the sum of odd digits (step 2) =MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1) - C1 has 2x the results of step 2 =B1*3 - D1 has 1x the even digits (step 4) =MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1) - E1 has the sum of steps 3 & 4 (step 5) =C1+D1 - F1 computes the check digit (step 6) =IF(MOD(E1,10)=0,0,10-MOD(E1,10)) The last computes the remainder after dividing E1 by 10, if zero - return zero, otherwise subtract that remainder from 10 which is the smallest number that will round the result of step 5 up to a multiple of 10. You can compress this quite easily into fewer cells - two is perhaps the most efficient with [note - I relocated the "two cells" to allow an easy comparison between the two methods] [2] The "two cell" solution - A1 has the 11 digit number - H1 has steps 2 though 5 - split into two lines, combine into a single cell. =3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1)) +MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1) - G1 has =IF(MOD(H1,10)=0,0,10-MOD(H1,10)) In this way, G1 and F1 are adjacent and should have the same result for easy comparison. If you must do this with a single formula, the following can be used which repeats the formula in H1 twice in the IF statement. Its been split into several lines to make it a little more clear the parts of the formula - combine into a single line / single cell for proper operation. =IF(MOD(3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1)) +MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1),10)=0,0,10- MOD(3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1)) +MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1),10)) Let's walk through the parts of the formula so you can build similar formulas for other needs. I suggest you search the Excel online help for additional information on these functions. The IF function takes three values (V1, V2, V3). If V1 is true, the result is V2; if V1 is false, the result is V3. The MOD function takes two values (V1, V2). The result is the remainder of dividing V1/V2. In our case, we're dividing by 9, so the result is between 0 and 9 inclusive. The MID function takes three values (V1, V2, V3). MID normally operates on a string valued V1 - but Excel will also convert a number to a string automatically. The result is the sequence of V3 letters of V1 starting at position V2. In this example, we always extract a single letter (V3=1) at a specific position - different for each MID function call. The + operator normally operates on numbers, but Excel also automatically converts strings (letters) to numbers so the digits of the value are summed properly. I've compared several values to the results from that web page you referenced. The formulas (all three forms) appear to work quite well - and produce consistent results. If you have any problems understanding the answer or if you need more information on some part of the question I did not adequately cover - please make a clarification request. I would be glad to help further. Good luck with your work. --Maniac``` Request for Answer Clarification by captcliff-ga on 13 Oct 2006 23:27 PDT ```Very well done. Thank you. It has been awhile since I used this service. I did not see a way to pay you so I am assuming that the money is paid automatically now. If someone is not familier with answers.google, how would they find it as I do not see it listed under "more" any longer?``` Clarification of Answer by maniac-ga on 14 Oct 2006 09:28 PDT ```Hello Captcliff, Thanks for the kind words. About finding Google Answers, I suggest you take that question to the editors / support staff for Google Answers directly. You'll find the email link at the bottom of each question page. --Maniac```
 captcliff-ga rated this answer: and gave an additional tip of: \$5.00 ```Maniac-ga was quick, through, and explained the solution is such a way that I could build on it to solve other questions. I was impressed!!!```

 ```if your value is on A1 you put =RoundUp((value(mid(A1,1,1))+value(mid(A1,3,1))+value(mid(A1,5,1))+value(mid(A1,7,1))+value(mid(A1,9,1))+value(mid(A1,11,1)))*3+(value(mid(A1,2,1))+value(mid(A1,4,1))+value(mid(A1,6,1))+value(mid(A1,8,1))+value(mid(A1,10,1)))/10,0)*10-(value(mid(A1,1,1))+value(mid(A1,3,1))+value(mid(A1,5,1))+value(mid(A1,7,1))+value(mid(A1,9,1))+value(mid(A1,11,1)))*3+(value(mid(A1,2,1))+value(mid(A1,4,1))+value(m id(A1,6,1))+value(mid(A1,8,1))+value(mid(A1,10,1)))```
 `Just wanted to thank reinedd-ga for his contribution and comment.`
 ```Hi guys, I looked at your formula and though it does solve the problem its a bit lengthy. Try: =MOD(10-MOD(SUMPRODUCT(--(MID(A1,{1,3,5,7,9,11},1)))*3+SUMPRODUCT(--(MID(A1,{2,4,6,8,10},1))),10),10) Type the 11 digit code in cell A1, and enter this formaula in A2, replicate the formula by copying down. The formula may be modified for 12 or 13 digit codes by adding integers (odd or even) within the brackets { } as appropriate. Use CONCATENATE to add the two text strings together in A3 and you are done... Hope this helps jgbix```