Mod 10 & 11
 

Is there a function that incorporates Mod 10 & 11 algorithms. The only thing
I am seeing is the Mod function.

Thanks,

what are the mod 10 &11 algorithms?

"Pablo" wrote:

Is there a function that incorporates Mod 10 & 11 algorithms. The only thing
I am seeing is the Mod function.

Thanks,

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

"bj" wrote:

what are the mod 10 &11 algorithms?

"Pablo" wrote:

Is there a function that incorporates Mod 10 & 11 algorithms. The only thing
I am seeing is the Mod function.

Thanks,

You will probably have to write a user defined function to do this

"Pablo" wrote:

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

"bj" wrote:

what are the mod 10 &11 algorithms?

"Pablo" wrote:

Is there a function that incorporates Mod 10 & 11 algorithms. The only thing
I am seeing is the Mod function.

Thanks,

Pablo wrote...
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)

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




"Pablo" wrote in message
...
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

"bj" wrote:

what are the mod 10 &11 algorithms?

"Pablo" wrote:

Is there a function that incorporates Mod 10 & 11 algorithms. The only
thing
I am seeing is the Mod function.

Thanks,

"Pablo" skrev i en meddelelse
...
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)


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Leo Heuser wrote...
"Pablo" skrev i en meddelelse
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)
....

So ISBN without check digit is 039304002.

Check digit = mod11 (11 - mod11 (144)) = 10
....
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.

"Harlan Grove" skrev i en meddelelse
ups.com...
Leo Heuser wrote...
"Pablo" skrev i en meddelelse
Mod 10 & Mod 11 are alogrithms for assigning a check-digit value to an
ISBN in the publishing world.

example

0 3 9 3 0 4 0 0 2 (ISBN)
...

So ISBN without check digit is 039304002.

Check digit = mod11 (11 - mod11 (144)) = 10
...
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!

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
9th digit

"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!

Leo Heuser wrote...
....
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
Mod 11 - calculates a sum based on the first nine digits, beginning
at the 9th digit

"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.

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.



"Harlan Grove" skrev i en meddelelse
oups.com...
Leo Heuser wrote...
...
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
Mod 11 - calculates a sum based on the first nine digits, beginning
at the 9th digit

"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.

Leo Heuser wrote...
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.

Leo Heuser wrote...
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.

At this risk of raising more controversy over this matter...!

I want to use this 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=382898