Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mod 10 & 11 | Excel Worksheet Functions | |||
How do I use a check box to accept a calculation | Excel Discussion (Misc queries) | |||
Least number of digits in Y-axis labels | Charts and Charting in Excel | |||
Auto spell check as in word | Excel Discussion (Misc queries) | |||
Creating a check box that does not require security clearance. | Excel Worksheet Functions |