Sum of digits in spreadsheet cell 11

[gs99]

Is there a way to get a sum of the digits in a cell?

If the number is 123, the sum is 6.
I beleive it is called "number reduction".

Thanks

 

[yogia]

Hi gs99:

I just saw this very interesting post. Following is a formula based solution ...

  |A              |B
--|---------------|-----------
 1|123	         6
 2|56972438	    44
 3|123567832456789 76

formula in cell B1 is ...

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

this is then copied down.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

And if you want the sum of the digits of the sum of the digits in column A, then ...

  |A               |B
--|----------------|------------------
 1|123	          6
 2|56972438	     8
 3|123567832456789  4

formula in cell B1 is ...

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

this is then copied down.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Ah! once more ...

running a test on the numbers furnished by gs99 in his post of 14 Jun 07 18:46 in this thread ...

   |A               |B
---|----------------|-------------------
  1|123	          6
  2|56972438	     8
  3|123567832456789  4
  4|1,134,903,170	2
  5|1,836,311,903	8
  6|2,971,215,073	1
  7|4,807,526,976	9

my convoluted formula in cell B1 is ...

=MOD(SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),9)+(MOD(SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),9)=0)*9

and this is then copied down.

It is possible that the formula can be simplified.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[gs99]

combo,

To avoid the "addition" problem, I'm using your VBA Add2Strings.
The VBA code in your For-Next does not reduce to a single digit.

I made this VBA function code with Str1 input:
Do Until Len(Str2) = 1
nDigitSum = 0 'reset in each loop
For i = 1 To Len(Str1) 'Str1 is input
nDigitSum = nDigitSum + CInt(Mid(Str1, i, 1))
Next i
'Copy to Str2; allow Loop to test if Len = 1
Str2 = CStr(nDigitSum)
'If another loop is needed, start with Str1 again
Str1 = Str2
Loop
'when loop is done, nDigitSum has one-digit integer
ReduceDigits = nDigitSum
End Function

It works for 96 cycles.

Question - Before doing the VBA solution, I tried to enter the array formula which needs the (CTRL+SHIFT+ENTER) into a cell by doing this:
strFormula = "{=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))}" (notice curly braces)
Then cells(row,3).formula = strFormula.
The formula appears in the cells but does not work as when I do it directly in Excel.

I don't want to do this "direct" solution but am curious how to convert an array formula to VBA.
Thanks

 

[gs99]

yogia,

The two-step "cell formula" solution by combo (and xlhelp) on Jun 15 works fine.

But a "program" solution (See Add2Strings function) seems to be required because of the problem Excel has incorrectly adding large numbers via cell formula =SUM(A1,B1).

Therefore a second function is needed to reduce the digits.

Anyway, that's my conclusion.

 

[combo]

The formula has to be a regular one, curly braces are added/deleted by excel. They only indicate an array formula and depend on how you exit the formula bar.

Concerning testing the periodicity of the sum of digits, after xlhelp's post one can notice that the sum behaves like 'Modulo 9' function, the only difference is that instead 0 it should return 9. This observation allows to use 'Modulo' function properties and represent all numbers by single digit numbers, with modulo math.

Start with A1=1 and A2=1, put A3=1+MOD(A1+A2,9) and copy it down. The result will be immediately the sum of digits of f(i) - i-th Fibonacci number.
The periodicity proof (you can't guarantee with pure excel that after 10000 cycles the behaviour will change) can be based on the observation that the sum of digits for f(i+2) item depends only on the sum for f(i) (9 options) and sum for f(i+1) (9 options), total 81 options. So cycle must exist.
With excel there can be found 24-cycles for starting points (f(1), f(2)): (1,1) and (1,3), 23-cycle when starting with (1,3), 9-cycle for (3,3) and 1-cycle for (9,9).

combo

 

[gs99]

combo,

The Fibonacci series starts: 1,1,2,3,5,8,13,21 ...
The expected reduced digits: 1,1,2,3,5,8, 4, 3 ...

Your formula:
Enter in A Excel gives
------------- -----------
1 1 1
2 1 1
3 =1+MOD(A1+A2,9) 3
4 =1+MOD(A2+A3,9) 5
5 =1+MOD(A3+A4,9) 9
6 =1+MOD(A4+A5,9) 6

The formula is copied from A3 down.
What's wrong with this?

 

[combo]

Sorry, I missed "1". Should be:
=1+MOD(A1+A2-1,9)

combo

 

[xlhelp]

Hi, GS 99.

In Math, number 9 is sort of a strange number; almost magical.

When you are dealing with large number and need to find the digital root (number reduction as you call it) one of the neat tricks is to eliminate any combination of 9 that occurs in the number string.

To take your example of
806515533049393
The second digit 0 and 12th digit 9 add up to 9. so eliminating them you are left with
8651553304393
Again eliminating 0 & 9 you are left with
86515533433
Then eliminating 8 & 1, you get
655533433
Getting rid of 6 & 3 gives
5553433
and lastly getting rid of 5 & 4 nets you
55333
which when added together gives the answer you were looking for ---> 1

I am not the greatest programmer in the world, but I make do. The following routine will perform above procedure to the active cell. May be someone can improve on it.

Sub niner()

Dim lg
Dim ns1
Dim ns2

On Error Resume Next

lg = ActiveCell.Value

For j = 0 To 4

lcs = InStr(1, lg, j)
cmp = InStr(1, lg, 9 - j)

If lcs = 0 Then
GoTo nxtj
End If

If cmp = 0 Then
GoTo nxtj
End If

ns1 = (Left(lg, lcs - 1) & Right(lg, Len(lg) - lcs)) * 1

cmp = InStr(1, ns1, 9 - j)
ns2 = (Left(ns1, cmp - 1) & Right(ns1, Len(ns1) - cmp)) * 1

lg = ns2

If j = 4 Then
GoTo nxtj
End If

If j < 4 Then
j = j - 1
End If

nxtj: Next j
ActiveCell.Value = ns2

End Sub

If you want to learn more about number 9, please go to

http://www.eklavya.org/magic9-1.htm

Have fun



Member- AAAA Association Against Acronym Abusers