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