Don't be so impressed... unfortunately, there is a problem with the formula.
The "casting out nines" part is correct, but it gets applied to too many of
the digits. If you have a 9 in a position that does not get multiplied by 2,
it should remain a 9 for the addition... unfortunately, my modification to
your formula makes it a 0 (that is what the MOD 9 does) because there is no
filter within it as to whether the digit gets multiplied by 2 or not.
--
Rick (MVP - Excel)
"Bernard Liengme" wrote in message
...
Rick,
I am impressed! When I read your explanation (casting out nines) my old
memory had a flash but I would never have got your formula without weeks
of work. Thanks
Bernard
"Rick Rothstein" wrote in message
...
If the number in A2 is always 7 digits then this will give the
sum-of-digits times {1,2,1,2,1,2,1}
=SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1})
But that ignores the complication of "each digit is added onto the total
separately (eg 8 * 2 = 16 = 1+6 = 7)"
This modification to your formula handles the "complication" as you call
it (again, for 7-digit numbers only)...
=SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9))
Summing the digits of a number repeatedly in order to produce a single
digit is called (in mathematical circles) "casting out nines" and it is
equivalent to finding the modulus (what the MOD function returns) of the
number with respect to 9. That is...
SumOfDigits: =MOD(Number,9)
So I changed your comma version of the SUMPRODUCT function to the
multiplication version and then just applied the above MOD function call
to the original value being presented to the SUMPRODUCT function.
--
Rick (MVP - Excel)
"Bernard Liengme" wrote in message
...
If the number in A2 is always 7 digits then this will give the
sum-of-digits times {1,2,1,2,1,2,1}
=SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1})
But that ignores the complication of "each digit is added onto the total
separately (eg 8 * 2 = 16 = 1+6 = 7)"
If this answer is in C2, we can test for multiple of 10 with
=IF(MOD(C2,10),"No","Yes")
I might find time on Monday to write a UDF to include the complications
mentioned above. Please revisit the newsgroup or email me direct (get my
email from my website)
If you are unfamiliar with UDF, you may wish to read one or more of
these
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Debra Dalgleish's "Adding Code to a Workbook"
http://www.contextures.com:80/xlvba01.html
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
Jon Peltier's site:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/
(General, Regular and Standard modules all describe the same thing.)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"RobertoB" wrote in message
...
Hi I have a complex validation process at hand and I wonder if there is
any
way I can do this with Excel:
My data looks as follows
A1 B1 C1 D1
Code Decription Value Multiple Yes/No
6460880 Base1
6460885 Base2
I need to fill the values for columns C and D using a validation
formula for
the number on Column A
The validation for numbers goes like this - starting from the back of
the
number, all the digits of the number are added together. Every other
number
is multiplied by two, and if that makes it a two digit number, each
digit is
added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total
must be
a multiple of 10. So, for example for the Code 6460880 we get the
following;
6460880 =
6 4 6 0 8 8 0
*1 *2 *1 *2 *1 *2 *1
=
6+ 8+ 6+0 +8 +7+0
=35 (which is not a multiple of 10 and therefore the value for C is £5
and
for D is NO)
But for the next value
6460885
=
6 4 6 0 8 8 5
*1 *2 *1 *2 *1 *2 *1
=
6+ 8+ 6+0 +8 +7+5
=40 (So the value for C is 40 and the value for D is YES as it is a
multiple
of 10) So the table will look as follows:
A1 B1 C1 D1
Code Decription Value Multiple of 10 Yes/No
6460880 Base1 35 NO
6460885 Base2 40 YES
I am using Offfice Excel 2003
Thank you for any advice you can provide