Mod 11 formula for valid check digit

vijayparihar · Oct 18, 2007

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 · Oct 18, 2007

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 · Oct 18, 2007

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 · Oct 18, 2007

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

Sheet1

vijayparihar · Oct 18, 2007

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 · Oct 18, 2007

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 · Oct 18, 2007

This UDF might help

Code:

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 · Oct 18, 2007

try:

Code:

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))

Code:

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 · Oct 18, 2007

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 · Oct 18, 2007

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

Mod 11 formula for valid check digit

vijayparihar

New Member

Excel Facts

njimack

Well-known Member

vijayparihar

New Member

njimack

Well-known Member

vijayparihar

New Member

njimack

Well-known Member

mikerickson

MrExcel MVP

facethegod

Well-known Member

vijayparihar

New Member

njimack

Well-known Member

Similar threads

Forum statistics

Share this page

Mod 11 formula for valid check digit

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

MrExcel MVP

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock