I have the following formula that is used to calculate a MOD 10 check digit
routine. For example: If I entered (8 digits) 45678912 in cell A1, the
formula would return a 2 as the 9th digit.

This is the formula that I am using but did not write so I'm having trouble
following it.
=10-MOD(SUM(--MID(TEXT($A1,"000000000"),{2,4,6,8},1),--RIGHT(MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),--LEFT(TEXT((MID(TEXT($A1,"000000000"),{1,3,5,7,9},1 )*2),"00"))),10)

I need to modify this formula so that it will look at 15 digits and
calculate the 16th. I've tried multiple things but have had no luck. Any
suggestions?

See this:

http://www.google.com/search?hl=en&s... aq=f&oq=&aqi=

--
Biff
Microsoft Excel MVP


"rojobrown" wrote in message
...
I have the following formula that is used to calculate a MOD 10 check digit
routine. For example: If I entered (8 digits) 45678912 in cell A1, the
formula would return a 2 as the 9th digit.

This is the formula that I am using but did not write so I'm having
trouble
following it.
=10-MOD(SUM(--MID(TEXT($A1,"000000000"),{2,4,6,8},1),--RIGHT(MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),--LEFT(TEXT((MID(TEXT($A1,"000000000"),{1,3,5,7,9},1 )*2),"00"))),10)

I need to modify this formula so that it will look at 15 digits and
calculate the 16th. I've tried multiple things but have had no luck. Any
suggestions?

On Oct 29, 9:11*pm, "T. Valko" wrote:
See this:

http://www.google.com/search?hl=en&s...d+10+check+dig...

--
Biff
Microsoft Excel MVP

"rojobrown" wrote in message

...



I have the following formula that is used to calculate a MOD 10 check digit
routine. *For example: If I entered (8 digits) 45678912 in cell A1, the
formula would return a 2 as the 9th digit.

This is the formula that I am using but did not write so I'm having
trouble
following it.
=10-MOD(SUM(--MID(TEXT($A1,"000000000"),{2,4,6,8},1),--RIGHT(MID(TEXT($A1,"*000000000"),{1,3,5,7,9},1)*2) ,--LEFT(TEXT((MID(TEXT($A1,"000000000"),{1,3,5*,7,9}, 1)*2),"00"))),10)

I need to modify this formula so that it will look at *15 digits and
calculate the 16th. *I've tried multiple things but have had no luck. Any
suggestions?- Hide quoted text -

- Show quoted text -

Your formula will return 10 in some instances, presumably that should
be zero. This formula should return the same results as yours (that
one anomaly apart) for 8 digit numbers....and also works for any
number of digits. Try for 15 and see if it works as you expect.......

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MO D
(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00") ,{1,2},1)),10)

or specifically for 15 digits....

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14; 15},
1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)) ,10)

regards, barry

I was wondering what ever happened to "daddylonglegs" that used to post
those amazing date formulas!

--
Biff
Microsoft Excel MVP


"barry houdini" wrote in message
...
On Oct 29, 9:11 pm, "T. Valko" wrote:
See this:

http://www.google.com/search?hl=en&s...d+10+check+dig...

--
Biff
Microsoft Excel MVP

"rojobrown" wrote in message

...



I have the following formula that is used to calculate a MOD 10 check
digit
routine. For example: If I entered (8 digits) 45678912 in cell A1, the
formula would return a 2 as the 9th digit.

This is the formula that I am using but did not write so I'm having
trouble
following it.
=10-MOD(SUM(--MID(TEXT($A1,"000000000"),{2,4,6,8},1),--RIGHT(MID(TEXT($A1,"*000000000"),{1,3,5,7,9},1)*2) ,--LEFT(TEXT((MID(TEXT($A1,"000000000"),{1,3,5*,7,9}, 1)*2),"00"))),10)

I need to modify this formula so that it will look at 15 digits and
calculate the 16th. I've tried multiple things but have had no luck. Any
suggestions?- Hide quoted text -

- Show quoted text -

Your formula will return 10 in some instances, presumably that should
be zero. This formula should return the same results as yours (that
one anomaly apart) for 8 digit numbers....and also works for any
number of digits. Try for 15 and see if it works as you expect.......

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MO D
(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00") ,{1,2},1)),10)

or specifically for 15 digits....

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14; 15},
1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)) ,10)

regards, barry

On Oct 29, 10:39*pm, "T. Valko" wrote:
I was wondering what ever happened to "daddylonglegs" that used to post
those amazing date formulas!

Hello Biff,

daddylonglegs was my excelforum "alter ego" - he's still going strong
over there - but I'm gonna be barry from now on everywhere else....
(except perhaps the email address)..it's too much trouble keeping BOTH
egos in check....

regards, barry