Calculate sum of digits in a number string

**shg** · 02-23-2010, 01:32 AM

Compliments of DLL, in some distant thread:

=A1 - FLOOR(A1 - 1, 9)

**PRADEEPB270** · 02-23-2010, 01:55 AM

Sir,I am not clear with this formula.Can you solve this problem on a work sheet ?

Regards

Pradeep

**PRADEEPB270** · 02-23-2010, 01:59 AM

Please accept my apolozies to written again about not clear.Now the formula is working fine.I am obliged.

Regards

Pradeep

**DonkeyOte** · 02-23-2010, 03:13 AM

Originally Posted by shg

Compliments of DLL, in some distant thread:

=A1 - FLOOR(A1 - 1, 9)

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

**shg** · 02-23-2010, 12:21 PM

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.

**JBeaucaire** · 02-23-2010, 12:28 PM

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"!

**feel3sam** · 10-20-2011, 03:07 PM

Thank you,

the formula is very helpful, is there a way to get the all 4 digit numbers with total 9?

regards
sam

Re: Calculate sum of digits in a number string