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

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

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.

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