P
Pablo
Is there a function that incorporates Mod 10 & 11 algorithms. The only thing
I am seeing is the Mod function.
Thanks,
I am seeing is the Mod function.
Thanks,
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
bj
what are the mod 10 &11 algorithms?
P
Pablo
Mod 10 & Mod 11 are alogrithms for assigning a check-digit value to an ISBN
in the publishing world.
Mod 11 - calculates a sum based on the first nine digits, beginning at the
9th digit
example
0 3 9 3 0 4 0 0 2 (ISBN)
10 9 8 7 6 5 4 3 2 Weight
0 27 72 21 0 20 0 0 4 = 144
Check digit = mod11 (11 – mod11 (144)) = 10
Mod 10 - calculates the check digit, 13th off of
9 7 8 0 3 9 3 0 4 0 0 2 (ISBN)
1 3 1 3 1 3 1 3 1 3 1 3 Weight
9 21 8 0 3 27 3 0 4 0 0 6 = 81
Check digit = mod10 (10 – mod10 (81)) = 9
Hope this explains it. I have seen this in VBA, but I want to see if I can
do it in a cell.
Thanks,
Paul
in the publishing world.
Mod 11 - calculates a sum based on the first nine digits, beginning at the
9th digit
example
0 3 9 3 0 4 0 0 2 (ISBN)
10 9 8 7 6 5 4 3 2 Weight
0 27 72 21 0 20 0 0 4 = 144
Check digit = mod11 (11 – mod11 (144)) = 10
Mod 10 - calculates the check digit, 13th off of
9 7 8 0 3 9 3 0 4 0 0 2 (ISBN)
1 3 1 3 1 3 1 3 1 3 1 3 Weight
9 21 8 0 3 27 3 0 4 0 0 6 = 81
Check digit = mod10 (10 – mod10 (81)) = 9
Hope this explains it. I have seen this in VBA, but I want to see if I can
do it in a cell.
Thanks,
Paul
B
bj
You will probably have to write a user defined function to do this
H
Harlan Grove
Pablo wrote...
Try
=10-MOD(SUMPRODUCT(--MID(SUBSTITUTE(ISBN_without_check_digit,"-",""),
{12;11;10;9;8;7;6;5;4;3;2;1},1),{3;1;3;1;3;1;3;1;3;1;3;1}),10)
Mod 10 & Mod 11 are alogrithms for assigning a check-digit value to an ISBN
in the publishing world.
Mod 11 - calculates a sum based on the first nine digits, beginning at the
9th digit
example
0 3 9 3 0 4 0 0 2 (ISBN)
10 9 8 7 6 5 4 3 2 Weight
0 27 72 21 0 20 0 0 4 = 144
Check digit = mod11 (11 - mod11 (144)) = 10
Try
=11-MOD(SUMPRODUCT(--MID(SUBSTITUTE(ISBN_without_check_digit,"-",""),
{9;8;7;6;5;4;3;2;1},1),{2;3;4;5;6;7;8;9;10}),11)
Mod 10 - calculates the check digit, 13th off of
9 7 8 0 3 9 3 0 4 0 0 2 (ISBN)
1 3 1 3 1 3 1 3 1 3 1 3 Weight
9 21 8 0 3 27 3 0 4 0 0 6 = 81
Check digit = mod10 (10 - mod10 (81)) = 9
Try
=10-MOD(SUMPRODUCT(--MID(SUBSTITUTE(ISBN_without_check_digit,"-",""),
{12;11;10;9;8;7;6;5;4;3;2;1},1),{3;1;3;1;3;1;3;1;3;1;3;1}),10)
N
N Harkawat
Check 11
=11-MOD(SUMPRODUCT(MID(A5,10-(ROW(INDIRECT("1:9"))),1)*(ROW(INDIRECT("2:10")))),11)
gives me a result of 10
check 10
=10-(MOD(SUM((MID(A1,ROW(INDIRECT("1:12")),1))*(IF(MOD(LEN(MID(A1,1,ROW(INDIRECT("1:12")))),2)=0,3,1))),10))
array entered (ctrl+shift+enter)
gives a result of 9
=11-MOD(SUMPRODUCT(MID(A5,10-(ROW(INDIRECT("1:9"))),1)*(ROW(INDIRECT("2:10")))),11)
gives me a result of 10
check 10
=10-(MOD(SUM((MID(A1,ROW(INDIRECT("1:12")),1))*(IF(MOD(LEN(MID(A1,1,ROW(INDIRECT("1:12")))),2)=0,3,1))),10))
array entered (ctrl+shift+enter)
gives a result of 9
L
Leo Heuser
Pablo said:Mod 10 & Mod 11 are alogrithms for assigning a check-digit value to an
ISBN
in the publishing world.
Mod 11 - calculates a sum based on the first nine digits, beginning at the
9th digit
example
0 3 9 3 0 4 0 0 2 (ISBN)
10 9 8 7 6 5 4 3 2 Weight
0 27 72 21 0 20 0 0 4 = 144
Check digit = mod11 (11 - mod11 (144)) = 10
Mod 10 - calculates the check digit, 13th off of
9 7 8 0 3 9 3 0 4 0 0 2 (ISBN)
1 3 1 3 1 3 1 3 1 3 1 3 Weight
9 21 8 0 3 27 3 0 4 0 0 6 = 81
Check digit = mod10 (10 - mod10 (81)) = 9
Hope this explains it. I have seen this in VBA, but I want to see if I
can
do it in a cell.
Thanks,
Paul
Paul
With ISBN (without hyphens) in A1:
=11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("4:12")),1)*{10;9;8;7;6;5;4;3;2}),11)
=10-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:12")),1)*{1;3;1;3;1;3;1;3;1;3;1;3}),10)
H
Harlan Grove
Leo Heuser wrote.......
So ISBN without check digit is 039304002.
With ="039304002" in A1, your formula above returns #VALUE!. Gotta
watch those typos in the INDIRECT calls!
You meant ROW(INDIRECT("1:9")), but {1;2;3;4;5;6;7;8;9} is shorter and,
perhaps, less error-prone.
So ISBN without check digit is 039304002.
........
With ISBN (without hyphens) in A1:
=11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("4:12")),1)*{10;9;8;7;6;5;4;3;2}),11)
With ="039304002" in A1, your formula above returns #VALUE!. Gotta
watch those typos in the INDIRECT calls!
You meant ROW(INDIRECT("1:9")), but {1;2;3;4;5;6;7;8;9} is shorter and,
perhaps, less error-prone.
L
Leo Heuser
Harlan Grove said:Leo Heuser wrote...
...
So ISBN without check digit is 039304002.
...
With ="039304002" in A1, your formula above returns #VALUE!. Gotta
watch those typos in the INDIRECT calls!
There's no typo, if I have understood the OP's mail correctly
The ISBN number (containing 12 digits) in A1 is
9 7 8 0 3 9 3 0 4 0 0 2
not as you claim
0 3 9 3 0 4 0 0 2
and >>>Mod 11 - calculates a sum based on the first nine digits, beginning
at the
"first nine digits" indicating that there are more than nine.
Since the sum starts from the ninth digit, which is digit number 4 in
the string in A1 (the number 0)
ROW(INDIRECT("4:12")
is correct.
You meant ROW(INDIRECT("1:9")), but {1;2;3;4;5;6;7;8;9} is shorter and,
perhaps, less error-prone.
I may have misunderstood the OP's mail, but I meant, what I wrote!
H
Harlan Grove
Leo Heuser wrote...
....
You have *NOT* understood the OP's original message correctly.
Mod 11 is applied to old 10-digit ISBNs (including check digit). Mod 10
is applied to new 13-digit ISBNs (including check digit). You're
applying Mod 11 to 13-digit ISBNs, which is as correct as applying SQRT
to negative numbers when expecting real-valued results.
Yes. The check digit in 10-digit ISBNs would make 10, and 10 > 9 to
most people. This is also true in Denmark, no?
Think logically. If you start at the 9th digit and you take 9 digits
going left, you pick up all digits but the rightmost digits, *AND* your
MID call would take digits from positions 1 through 9, not 4 through
12. On the other hand, if you start at the 9th digit and take 9 digits
going right, you'd need digits in positions 14 through 17, but there
are no such digits even in 13-digit ISBNs, and you'd include the check
digit in position 13 in the calculation of itself.
So starting with the OP's specs, it should be obvious to most people
who'd think about it that you can't start at the 9th digit and go
right. If you start at the 9th digit and go left, don't you want your
MID call to be
MID(.,{9;8;7;6;5;4;3;2;1},1) or MID(.,{1;2;3;4;5;6;7;8;9})
??
To a native English speaker "the first nine digits, beginning at the
9th digit" can only mean digits 1 through 9 in reverse order, so digits
9, 8, 7, 6, 5, 4, 3, 2, 1. ROW(INDIRECT("4:12")) is wrong. This pulls
the middle 9 digits beginning at the 4th digit, or if it were reversed,
the middle 9 digits beginning at the 12th digit. It never pulls the
*first* 9 digits.
You just don't want to admit you screwed up. Maybe there's an outside
chance you still don't realize you screwed up.
....
There's no typo, if I have understood the OP's mail correctly
The ISBN number (containing 12 digits) in A1 is
9 7 8 0 3 9 3 0 4 0 0 2
not as you claim
0 3 9 3 0 4 0 0 2
You have *NOT* understood the OP's original message correctly.
Mod 11 is applied to old 10-digit ISBNs (including check digit). Mod 10
is applied to new 13-digit ISBNs (including check digit). You're
applying Mod 11 to 13-digit ISBNs, which is as correct as applying SQRT
to negative numbers when expecting real-valued results.
and
"first nine digits" indicating that there are more than nine.
Yes. The check digit in 10-digit ISBNs would make 10, and 10 > 9 to
most people. This is also true in Denmark, no?
Think logically. If you start at the 9th digit and you take 9 digits
going left, you pick up all digits but the rightmost digits, *AND* your
MID call would take digits from positions 1 through 9, not 4 through
12. On the other hand, if you start at the 9th digit and take 9 digits
going right, you'd need digits in positions 14 through 17, but there
are no such digits even in 13-digit ISBNs, and you'd include the check
digit in position 13 in the calculation of itself.
So starting with the OP's specs, it should be obvious to most people
who'd think about it that you can't start at the 9th digit and go
right. If you start at the 9th digit and go left, don't you want your
MID call to be
MID(.,{9;8;7;6;5;4;3;2;1},1) or MID(.,{1;2;3;4;5;6;7;8;9})
??
....Since the sum starts from the ninth digit, which is digit number 4 in
the string in A1 (the number 0)
ROW(INDIRECT("4:12")
is correct.
To a native English speaker "the first nine digits, beginning at the
9th digit" can only mean digits 1 through 9 in reverse order, so digits
9, 8, 7, 6, 5, 4, 3, 2, 1. ROW(INDIRECT("4:12")) is wrong. This pulls
the middle 9 digits beginning at the 4th digit, or if it were reversed,
the middle 9 digits beginning at the 12th digit. It never pulls the
*first* 9 digits.
You just don't want to admit you screwed up. Maybe there's an outside
chance you still don't realize you screwed up.
L
Leo Heuser
When you start to include your well-known name calling, insults
and rude manners, the "discussion" ends right here, as far as I'm
concerned.
and rude manners, the "discussion" ends right here, as far as I'm
concerned.
H
Harlan Grove
Leo Heuser wrote...
IOW, your overly sensitive feelings are much more important than the
accuracy of your postings.
....When you start to include your well-known name calling, insults
and rude manners, the "discussion" ends right here, as far as I'm
concerned.
IOW, your overly sensitive feelings are much more important than the
accuracy of your postings.
H
Harlan Grove
Leo Heuser wrote...
& BTW, where were the insults or name calling? Sarcasm, certainly, but
the only possibly rude comment was stating the FACT that you screwed up.
....When you start to include your well-known name calling, insults
and rude manners, the "discussion" ends right here, as far as I'm
concerned.
& BTW, where were the insults or name calling? Sarcasm, certainly, but
the only possibly rude comment was stating the FACT that you screwed up.
C
Colin Vicary
At this risk of raising more controversy over this matter...!
I want to use this function to test that the user has entered
"correct" ISBN by validating the check digit.
I want to us
=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 origina
function could be 11 which is transalted into "X" on a book. How woul
I need to change my formula to cope with that?
Thanks
Coli
I want to use this function to test that the user has entered
"correct" ISBN by validating the check digit.
I want to us
=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 origina
function could be 11 which is transalted into "X" on a book. How woul
I need to change my formula to cope with that?
Thanks
Coli