Mod 11 formula for valid check digit

[vijayparihar]

Hey guys, i am looking for a Mod 11 excel formula for valid check digit(for isbn 10)

example:
The check digit is the last digit of an ISBN. It is calculated on a modulus 11 with weights 10-2, using X in lieu of 10 where ten would occur as a check digit.

This means that each of the first nine digits of the ISBN – excluding the check digit itself – is multiplied by a number ranging from 10 to 2 and that the resulting sum of the products, plus the check digit, must be divisible by 11 without a remainder.
For example ISBN 0843610727


ISBN No. : 0 8 4 3 6 1 0 7 2 7
Weight : 10 9 8 7 6 5 4 3 2


i.e.(0*10 + 8*9 +4 *8 + 3*7 + 6*6 + 1*5 + 0*4 + 7*3 + 2*2 + 7)
--------------------------------------------------------------------------------

Products 0 +72 +32 +21 +36 +5 +0 +21 +4 +7


Total: 198

As 198 can be divided by 11 without remainder 0-8436-1072-7 is a valid ISBN.
7 is the valid check digit.

will really appreciate if some one can help me with and excel formula for the same, thanks guys..[/list]

 

[vijayparihar]

Thanks buddy. but neither works. first returns a value error and second one returns strange check digits when used with 10 digits and n/a when used with 9

try:

return's the valid check digit

=IF((11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*{10;9;8;7;6;5;4;3;2;1}),11))=10,"X",11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*{10;9;8;7;6;5;4;3;2;1}),11))

determines if the check digit is valid

=IF(MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*{10;9;8;7;6;5;4;3;2;1}),11),"invalid check digit","valid check digit")

 

[vijayparihar]

Neil, thanks man. works so far for the data i have been testing. there is one error though, which is the rule :

It is calculated on a modulus 11 with weights 10-2, using X in lieu of 10 where ten would occur as a check digit. i where ever 10 occurs as a check digit it returns me false value rather then true which it actually is.


example:
702004087X

any solutions for this too?

btw you are the man

 

[vijayparihar]

thanks

Neil, thanks man. works so far for the data i have been testing. there is one error though, which is the rule :

It is calculated on a modulus 11 with weights 10-2, using X in lieu of 10 where ten would occur as a check digit. i where ever 10 occurs as a check digit it returns me false value rather then true which it actually is.


example:
702004087X

any solutions for this too?

btw you are the man

You need to format the column as text to stop Excel removing the leading zero. Either use Format>Cells BEFORE entering the ISBN, or use Data>Text to Columns AFTER entering the ISBN

 

[facethegod]

If your data has dashes in it try:

=IF((11-MOD(SUMPRODUCT(MID(SUBSTITUTE(G1,"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(G1,"-","")))),1)*{10;9;8;7;6;5;4;3;2;1}),11))=10,"X",MOD(SUMPRODUCT(MID(SUBSTITUTE(G1,"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(G1,"-","")))),1)*{10;9;8;7;6;5;4;3;2;1}),11)=0)

 

[vijayparihar]

data doesnt have dashes. all 10 digits together. and it returns the same error as earlier #Value!

If your data has dashes in it try:

=IF((11-MOD(SUMPRODUCT(MID(SUBSTITUTE(G1,"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(G1,"-","")))),1)*{10;9;8;7;6;5;4;3;2;1}),11))=10,"X",MOD(SUMPRODUCT(MID(SUBSTITUTE(G1,"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(G1,"-","")))),1)*{10;9;8;7;6;5;4;3;2;1}),11)=0)

 

[mikerickson]

Using RIGHT("0000000000"&SUBSTITUTE(A1,"-",""),10) instead of SUBSTITUTE(A1,"-","") will make sure that any leading zeros are replaced. (This looks like a job for Named Functions.)

Excel returns FALSE when it actually is true. That probably is because excel removes the 0 from 0843610727. it works for all isbn's which do not start with 0.

Any solutions for this problem? i tried " and it works alright, but yet again i donno out of the 5000 no.'s i have how will i put a " to all 0's?? i guess there should be a way for it????

Thanks yet yet yet again dude.

Ok, 3rd time lucky.

This approach isn't as neat, but it does have the advantage of only requiring one cell. One of the gurus on here could probably offer a much better attempt.

Assuming A1 contains "0-8436-1072-7"
Formula (anywhere) (enter normally, no need for CSE): =MOD(LEFT(A1)*10+(MID(SUBSTITUTE(A1,"-",""),2,1)*9)+(MID(SUBSTITUTE(A1,"-",""),3,1)*8)+(MID(SUBSTITUTE(A1,"-",""),4,1)*7)+(MID(SUBSTITUTE(A1,"-",""),5,1)*6)+(MID(SUBSTITUTE(A1,"-",""),6,1)*5)+(MID(SUBSTITUTE(A1,"-",""),7,1)*4)+(MID(SUBSTITUTE(A1,"-",""),8,1)*3)+(MID(SUBSTITUTE(A1,"-",""),9,1)*2)+RIGHT(A1),11)=0

 

[vijayparihar]

Thanks for the suggestion Mike, i was actually able to resolve that issue, but a new one seems to have popped up, that is related to the last digit being x when the check digit is 10.

Any suggestions?

Using RIGHT("0000000000"&SUBSTITUTE(A1,"-",""),10) instead of SUBSTITUTE(A1,"-","") will make sure that any leading zeros are replaced. (This looks like a job for Named Functions.)

Excel returns FALSE when it actually is true. That probably is because excel removes the 0 from 0843610727. it works for all isbn's which do not start with 0.

Any solutions for this problem? i tried " and it works alright, but yet again i donno out of the 5000 no.'s i have how will i put a " to all 0's?? i guess there should be a way for it????

Thanks yet yet yet again dude.

Ok, 3rd time lucky.

This approach isn't as neat, but it does have the advantage of only requiring one cell. One of the gurus on here could probably offer a much better attempt.

Assuming A1 contains "0-8436-1072-7"
Formula (anywhere) (enter normally, no need for CSE): =MOD(LEFT(A1)*10+(MID(SUBSTITUTE(A1,"-",""),2,1)*9)+(MID(SUBSTITUTE(A1,"-",""),3,1)*8)+(MID(SUBSTITUTE(A1,"-",""),4,1)*7)+(MID(SUBSTITUTE(A1,"-",""),5,1)*6)+(MID(SUBSTITUTE(A1,"-",""),6,1)*5)+(MID(SUBSTITUTE(A1,"-",""),7,1)*4)+(MID(SUBSTITUTE(A1,"-",""),8,1)*3)+(MID(SUBSTITUTE(A1,"-",""),9,1)*2)+RIGHT(A1),11)=0

 

[njimack]

Of course the formula won't work if the ISBN contains the number 10, since the formula I provided only works for 10-digit ISBN's. The example you posted contains 11 digits. Sorry, not sure how to deal with that.

 

[vijayparihar]

one more...

No worries man, i appreciate ur help. one last help i need is with this, it seems very simlar to your formula, can we do some thing about it ?? :

I have seen a few formulas online to covert an isbn 10 to isbn 13, like this one (http://ndpsoftware.com/isbn.php) but i was wondering if there is a macro for reverse, i.e. conversion from isbn 13 to 10, using MOD 11 the algorithm is:

Converting from Bookland EAN-13 to a 10-digit ISBN
978-0-393-04002-9
1. Strip the check-digit, as a new modulus 11 check-digit will be generated for the 10-digit ISBN. This gives us 978-0-393-04002-

2. Strip the “978” EAN Bookland prefix, giving a 10-digit ISBN string without its check-digit. ( Stripping the “978” prefix gives us 0-393-04002-

3. The 10th digit of the 10-digit ISBN is the modulo 11 check-digit, which will have a value of 0-10, with the value 10 being represented by “X”.
Modulus 11 algorithm to calculate check digit for the 10-digit ISBN (check digit unknown)
Incomplete 10-digit ISBN = 0-393-04002-?

weightage : (0*10 + 3*9 + 9*8 + 3*7 + 0*6 + 4*5 + 0*4 + 0*3 + 2*2) ie ( 10 * first digit, 9*second & so on till 2* 9th digit)

Check digit = mod11 (11 - mod11 (Product Total)) = mod11 (11 – mod11 (144)) = 10

(Special case note: When the check digit calculates to 10, it is represented as an “X”.)
(Technical note: mod11 of a test number returns the remainder of the test number divided by 11 unless the number is less than 11 in which case it returns the test number itself)
Complete 10-digit ISBN = 0-393-04002-X

would appreciate if some one can help with this or let me know if such formula already exsists. Thanks

Thanks for the suggestion Mike, i was actually able to resolve that issue, but a new one seems to have popped up, that is related to the last digit being x when the check digit is 10.

Any suggestions?

Using RIGHT("0000000000"&SUBSTITUTE(A1,"-",""),10) instead of SUBSTITUTE(A1,"-","") will make sure that any leading zeros are replaced. (This looks like a job for Named Functions.)

Excel returns FALSE when it actually is true. That probably is because excel removes the 0 from 0843610727. it works for all isbn's which do not start with 0.

Any solutions for this problem? i tried " and it works alright, but yet again i donno out of the 5000 no.'s i have how will i put a " to all 0's?? i guess there should be a way for it????

Thanks yet yet yet again dude.

Ok, 3rd time lucky.

This approach isn't as neat, but it does have the advantage of only requiring one cell. One of the gurus on here could probably offer a much better attempt.

Assuming A1 contains "0-8436-1072-7"
Formula (anywhere) (enter normally, no need for CSE): =MOD(LEFT(A1)*10+(MID(SUBSTITUTE(A1,"-",""),2,1)*9)+(MID(SUBSTITUTE(A1,"-",""),3,1)*8)+(MID(SUBSTITUTE(A1,"-",""),4,1)*7)+(MID(SUBSTITUTE(A1,"-",""),5,1)*6)+(MID(SUBSTITUTE(A1,"-",""),6,1)*5)+(MID(SUBSTITUTE(A1,"-",""),7,1)*4)+(MID(SUBSTITUTE(A1,"-",""),8,1)*3)+(MID(SUBSTITUTE(A1,"-",""),9,1)*2)+RIGHT(A1),11)=0

 

[vijayparihar]

any one for the macro