The summation of digits

[Jan Kalop]

Having in A11 digit 1909 and entering in B11 formula "=SUM(VALUE(MID(A11,ROW($I$1:OFFSET($I$1,LEN(A11)-1,0)),1)))" with enter and shift, display in B11 digit 19


then entering in,C11 formula "=SUM(VALUE(MID(B11,ROW($I$1:OFFSET($I$1,LEN(B11)-1,0)),1)))" with enter and shift, display in C11 digit 10


then entering in,D11 formula "=SUM(VALUE(MID(C11,ROW($I$1:OFFSET($I$1,LEN(C11)-1,0)),1)))" with enter and shift, display in D11 digit 1


How can I get the end result form cell D11 in cell B11 with one compact formula calculating all those 3 calculations in one attempt.

 

[shg]

The sum of digits iteratively applied is called the digital root.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1909​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCFFCC"]B11: =IF(A11 = 0, 0, A11 - FLOOR(A11-1, 9))[/td][/tr]
[/table]

This is simpler, but a bug in the MOD function appears for large numbers:

=IF(A11 = 0, 0, MOD(A11-1, 9) + 1)

See http://excel.tips.net/T003302_Large_Numbers_in_the_MOD_Function.html)

 

[James006]

Hello,

You can test following Array Formula:

=SUM(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1)))

HTH

 

[Special-K99]

Summing digits of a number, non array

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))

 

[Jan Kalop]

Great, thanks a lot to all suggestions, but it seams that the first one suits me the best. Fantastic, thank you.