Calculate sum of digits in a number string

I need to calculate the sum of digits in a numeric string. The function should keep on adding the digits of each result, till the final sum is a single digit. Please refer to the following example and result expected:-

Supposed the number entered in Cell A1 is 456789

Result 1 = 4+5+6+7+8+9 = 39

Result 2 = 3+9 = 12

Final Result required = 1+2 = 3

Regards

Pradeep Kumar Gupta

Compliments of DLL, in some distant thread:

=A1 - FLOOR(A1 - 1, 9)

hello,

Here is a function that will do what you are after,

Please Login or Register  to view this content.

cheers

I would take the DLL function , It should be a lot faster that the UDF. The power of excel :P

Originally Posted by shg

Compliments of DLL, in some distant thread:

=A1 - FLOOR(A1 - 1, 9)

er...FWIW... wow....

Actually, it's an example of a more general process (see http://www.mathpages.com/home/kmath020.htm and http://mathforum.org/k12/mathtips/mulliss.html ), and the simpler solution is =MOD(A1 - 1, 9) + 1.

DLL's alternative circumvents a bug in the MOD function described at http://support.microsoft.com/kb/119083. I don't know whether it persists to later versions.

Originally Posted by shg

Compliments of DLL, in some distant thread:

=A1 - FLOOR(A1 - 1, 9)

Every now and then, the elegance of a solution makes my jaw drop. As DO said..."wow"!

Gavald229,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.