Mod 10 & 11

[Pablo]

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

Thanks,

[bj]

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]

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,

[bj]

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,

[Harlan Grove]

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)

[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




"Pablo" <[email protected]> wrote in message
news:[email protected]...
> 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,

[Leo Heuser]

"Pablo" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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.

[Harlan Grove]

Leo Heuser wrote...
>"Pablo" <[email protected]> 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.

[Leo Heuser]

"Harlan Grove" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Leo Heuser wrote...
>>"Pablo" <[email protected]> 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!

[Harlan Grove]

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]

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" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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.
>

[Harlan Grove]

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.

[Harlan Grove]

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.

[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 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