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]

 

[njimack]

Someone may be able to post a better method, but the below seems to work.
Formula in A4:=MOD(SUM(B1:K1*B2:K2),11)=0 (confirmed with Ctrl+Shift+Enter)

*edit* an improved formula (without the need for the weighting to be a separate row) would be: =MOD(SUM(B1:K1*{10,9,8,7,6,5,4,3,2,1}),11)=0 Again, this needs to be confirmed with Ctrl+Shift+Enter

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ISBN	0	8	4	3	6	1	0	7	2	7
2	WEIGHT	10	9	8	7	6	5	4	3	2	1
3
4	TRUE
Sheet1

 

[vijayparihar]

Thanks for the propmt respose, but there is a slight issue,isbn is a whole no. ie 0843610727 and is in only one column(i.e b1) the weight is assigned but it does not exsist in the database and has to be implemented in the formula only.

for example i saw a similar kind of question in forum where the weightage provided is diffrent, but rest looks pretty same.

Number To Be Calculated: 0 0 4 4 5 5 5 2 5

Weighting: 2 1 2 1 2 1 2 1 2

Sum: 0+0+8+4+1+0+5+1+0+2+1+0

22/10 = 2 r2
10-2 = 8 (Check Digit)

Formula BY IML :
=10-MOD(SUM(--MID(TEXT($A1,"000000000"),{2,4,6,8},1),--RIGHT(MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),--LEFT(TEXT((MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),"00"))),10)

 

[njimack]

The example you posted is quite different, due to the fact that the weighting alternates between 1 and 2, although someone may be able to amend it to your needs.

Here's a variation on my initial post.
Formula in columns B to K (these could be hidden): =MID(SUBSTITUTE($A2,"-",""),COLUMN()-1,1)
Formula in column L: =MOD(SUM(B2:K2*{10,9,8,7,6,5,4,3,2,1}),11)=0 (confirm with CSE)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ISBN	Digit1	Digit2	Digit3	Digit4	Digit5	Digit6	Digit7	Digit8	Digit9	Digit10	CHECK
2	0-8436-1072-7	0	8	4	3	6	1	0	7	2	7	TRUE
Sheet1

 

[vijayparihar]

well thanks again, but i have atleast 10 columns in the excel and more 5000 isbn no.s, cant really make 10 more columns for all isbn no.s , there should be some way to do it without having the additional 9 columns??

really appreciate ur replies buddy.. thanks[/img]

 

[njimack]

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

 

[mikerickson]

This UDF might help

Function okISBN(testValue As String) As Boolean
Dim working As Variant, i As Long
testValue = Right("0000000000" & testValue, 10)
For i = 1 To 10
    working = working + (i * Mid(testValue, i, 1))
Next i
okISBN = ((working Mod 11) = 0)
End Function

 

[facethegod]

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]

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]

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