ISBN Check Digits

[Colin Vicary]

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

[Bernard Liengme]

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(SUMPRODUCT(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","invalid"))

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Colin Vicary]

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

[Bernard Liengme]

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

"Colin Vicary" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>