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!