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