Re: ISBN Check Digits

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <Colin.Vicary.1tm0ic_1123779908.0847@xxxxxxxxxxxxxxxxxxxxx>
wrote in message
news:Colin.Vicary.1tm0ic_1123779908.0847@xxxxxxxxxxxxxxxxxxxxxxxx
>
> 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?action=getinfo&userid=10472
> View this thread: http://www.excelforum.com/showthread.php?threadid=395037
>


.

Relevant Pages

  • Re: marking schemes for SMD resistors
    ... colin wrote: ... the coding system I came across on ... some 1% 0603 is rather anoying, you have to look up a 2 digit code to find ... the 3 digit value and look up the letter to find the multiplier value. ...
    (sci.electronics.design)
  • Re: marking schemes for SMD resistors
    ... colin wrote: ... the 3 digit value and look up the letter to find the multiplier value. ... That sounds both useful and interesting, Michael. ... javascript code. ...
    (sci.electronics.design)
  • ISBN Check Digits
    ... I've googled without any luck! ... ISBN by validating the check digit. ... Colin Vicary ... Colin Vicary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10472 ...
    (microsoft.public.excel.misc)
  • Re: Mod 10 & 11
    ... At this risk of raising more controversy over this matter...! ... but every possible last digit give the result "invalid". ... Colin Vicary ...
    (microsoft.public.excel.worksheet.functions)