Algorithm Challenge

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:e9r2ndSjFHA.1948@TK2MSFTNGP12.phx.gbl...

> The value must be entered as '0500450002883 to preserve the leading zero

You could always force it

=MOD(SUMPRODUCT(--(MID(TEXT(A1,"0000000000000"),ROW(INDIRECT("1:12")),1)),RO
W(INDIRECT("1:12"))),9)

Bob Phillips wrote...
....
>You could always force it
>
>=MOD(SUMPRODUCT(--(MID(TEXT(A1,"0000000000000"),ROW(INDIRECT("1:12")),1)),
>ROW(INDIRECT("1:12"))),9)

Why not just treat it as a number in the first place?

=MOD(SUMPRODUCT(INT(A1/10^{1;2;3;4;5;6;7;8;9;10;11;12})
-10*INT(A1/10^{2;3;4;5;6;7;8;9;10;11;12;13}),
{12;11;10;9;8;7;6;5;4;3;2;1}),9)

This is lots longer, but if you name the array something like ARRAY, it
becomes

=MOD(SUMPRODUCT(INT(A1/10^ARRAY)-10*INT(A1/10^(1+ARRAY)),13-ARRAY),9)

Bob Phillips wrote...
....
>You could always force it
>
>=MOD(SUMPRODUCT(--(MID(TEXT(A1,"0000000000000"),ROW(INDIRECT("1:12")),1)),
>ROW(INDIRECT("1:12"))),9)

Why not just treat it as a number in the first place?

=MOD(SUMPRODUCT(INT(A1/10^{1;2;3;4;5;6;7;8;9;10;11;12})
-10*INT(A1/10^{2;3;4;5;6;7;8;9;10;11;12;13}),
{12;11;10;9;8;7;6;5;4;3;2;1}),9)

This is lots longer, but if you name the array something like ARRAY, it
becomes

=MOD(SUMPRODUCT(INT(A1/10^ARRAY)-10*INT(A1/10^(1+ARRAY)),13-ARRAY),9)

Bob Phillips wrote...
....
>You could always force it
>
>=MOD(SUMPRODUCT(--(MID(TEXT(A1,"0000000000000"),ROW(INDIRECT("1:12")),1)),
>ROW(INDIRECT("1:12"))),9)

Why not just treat it as a number in the first place?

=MOD(SUMPRODUCT(INT(A1/10^{1;2;3;4;5;6;7;8;9;10;11;12})
-10*INT(A1/10^{2;3;4;5;6;7;8;9;10;11;12;13}),
{12;11;10;9;8;7;6;5;4;3;2;1}),9)

This is lots longer, but if you name the array something like ARRAY, it
becomes

=MOD(SUMPRODUCT(INT(A1/10^ARRAY)-10*INT(A1/10^(1+ARRAY)),13-ARRAY),9)