ISBN Check Digits
 

Hi everyone

I've googled without any luck!

I want to use a function to test that the user has entered a "correct"
ISBN by validating the check digit.

I want to use
=if(right(a1)<(11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:9")),1)*{10; 9;8;7;6;5;4;3;2}),11)),"Invalid","")
but every possible last digit give the result "invalid".

To complicate matters further in some cases the result of the original
function could be 11 which is transalted into "X" on a book. How would
I need to change my formula to cope with that?

Thanks

Colin


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=395037

My ISBN is in cell B4. The checkdigit is 11-mod(sumproduct(9 first digits),
but if this computes to 11 then the checkdigit is 0; if it computes to 10
the ISBN ends with X.
It's a long formula but it seems to work:
=IF(AND(RIGHT(B4)="x",
11-MOD(SUMPRODUCT(VALUE(MID(B4,ROW(A1:A9),1)),{10;9;8 ;7;6;5;4;3;2}),11)=10),"valid",IF(IF(MOD(SUMPRODUC T(VALUE(MID(B4,ROW(A1:A9),1)),{10;9;8;7;6;5;4;3;2} ),11)=0,0,11-MOD(SUMPRODUCT(VALUE(MID(B4,ROW(A1:A9),1)),{10;9;8 ;7;6;5;4;3;2}),11))=VALUE(RIGHT(B4)),"valid","inva lid"))

Since (1) we wish to retain leading zeros and (2) an ISBN might end in X, I
treat the ISBN as text. So unless it ends with X, I enter it with leading
apostrophe.
Some values to try it out
075065614x
0750656131
8441515530




best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Colin Vicary"
wrote in message
news:Colin.Vicary.1tm0ic_1123779908.0847@excelforu m-nospam.com...

Hi everyone

I've googled without any luck!

I want to use a function to test that the user has entered a "correct"
ISBN by validating the check digit.

I want to use
=if(right(a1)<(11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:9")),1)*{10; 9;8;7;6;5;4;3;2}),11)),"Invalid","")
but every possible last digit give the result "invalid".

To complicate matters further in some cases the result of the original
function could be 11 which is transalted into "X" on a book. How would
I need to change my formula to cope with that?

Thanks

Colin


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile:
http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=395037

Hi Dr. Bernard and thanks for your help, that worked perfectly!

BTW, I checked the first two ISBNs on our database (I work for a UK
book wholesaler).

Thought you might like to know that we've sold almost 350 copies in
total of those two ISBNs - couldn't find the third one though!

Thanks again

Colin


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=395037

The third one is a Polish translation!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Colin Vicary"
wrote in message
news:Colin.Vicary.1tn66g_1123833911.0337@excelforu m-nospam.com...

Hi Dr. Bernard and thanks for your help, that worked perfectly!

BTW, I checked the first two ISBNs on our database (I work for a UK
book wholesaler).

Thought you might like to know that we've sold almost 350 copies in
total of those two ISBNs - couldn't find the third one though!

Thanks again

Colin


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile:
http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=395037