Formula for calculating a barcode check digit

I'm trying to write a formula for calculating the check digit for S10 (UPU Standard) barcodes of the Universal Postal Union. Here's an example of a barcode:

barcode217794027.jpg

These barcodes incorporate a 9 digit number, with the right-most digit being the check-digit, which is calculated as follows:

---

1. Assign the weights 8, 6, 4, 2, 3, 5, 9, 7 to the 8 digits, from left to right

2. Calculate S, the sum of each digit multiplied by its weight.
For example, for the number 21779402, S = 2*8 + 1*6 + 7*4 + 7*2 + 9*3 + 4*5 + 0*9 + 2*7 = 125

3. Calculate the check digit, C, from C = 11 - (S mod 11) = 11 - (125 mod 11) = 11 - 4 = 7
If C = 10, change to C = 0
If C = 11, change to C = 5

Answer: check digit is 7

---

With my 8 digit number in cell A1, I use this formula in B1 to find the check digit:

=11-(MOD(((MID(A1,1,1)*8)+(MID(A1,2,1)*6)+(MID(A1,3,1)*4)+(MID(A1,4,1)*2)+(MID(A1,5,1)*3)+(MID(A1,6,1)*5)+(MID(A1,7,1)*9)+(MID(A1,8,1)*7)),11))

However, when the first digit of the barcode is zero, I get #VALUE! error. How can I fix that.

Also, I am trying to deal with C = 10 and C = 11 with a formula in cell C1: IF(B1=10,0,B1) IF(B1=11,5,B1) How can I combine these two formulas?

Originally Posted by samba_ravi

=LOOKUP(11-(MOD(SUM(MID(TEXT(A1,"00000000"),{1,2,3,4,5,6,7,8},1)*{8,6,4,2,3,5,9,7}),11)),{1,2,3,4,5,6,7,8,9,10,11},{1,2,3,4,5,6,7,8,9,0,5})

Your direct formula works perfectly. Thank you.

Hi I have the same problem I tested the formulas its not working for me, someone can help me??