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

 

[xlhelp]

I thought I would pop-in for a moment before shutting down. I just happen to have an answer for this one. I had to find it for someone a while back.

It is a recursive formula; that is, if the sum of digits is 12, then it adds 1 and 2 together to give you 3. Hope that's what you are looking for.

=IF(A1>0,1+MOD(A1-1,9),0)

I didn't know I was addicted to the forum.



Member- AAAA Association Against Acronym Abusers

 

[Gavona]

That's a fascinating formula. I have not got my head around it sufficiently to modify it. Unfortunately it does not appear to work if the answer is greater than 9.

If there is a limit to the number of digits a more clunky approach is possible (see below). However I look forward to seeing some more creative/elegant solutions!

This works for up to 3 digits and can be easily extended for more

=LEFT(A1,1)+IF(LEN(A1)>1,MID(A1,2,1),0)+IF(LEN(A1)>2,MID(A1,3,1),0)


Gavin

 

[gs99]

xlhelp,
Thanks, your code works.
I'm doing a spreadsheet on Fibonacci series, to display the discovery that there is a recurring 24 pattern.

It works OK until the 46th number at which point it gives a #NUM! error.
The 45th number is 1,134,903,170 (= 2).
The 46th number is 1,836,311,903. (should be 8)
The 47th number is 2,971,215,073. (should be 1)
The 48th number is 4,807,526,976. (should be 9)

Thanks

 

[Tarnish]

http://support.microsoft.com/kb/214053

Dunno if that has any value for you, but worth a shot.
T

 

[cresbydotcom]

If and when you get into programming create your own function - VBA is soooooooo much more flexible.

eg in the cell "=foo(A1)"

in a module

Function foo(a) As Integer
Dim num As Integer
VBA code here summing numbers in variable num
foo = num
End Function

 

[GlennUK]

Gavona, xlhelp's formula does appear to work. I don't think you took into account that it's recursive ( i.e. if the result is greater than 9 then those results have the figures added too, on so on ).

A nice purply star to xlhelp ====> *

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

Nice formula, xlhelp.
Gs99, if you like to work with numbers above Long limit, proceede with strings. Format input cell as text (say, A1).
Now in B1 put array formula (CTRL+SHIFT+ENTER) to get intermediary sum:
=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
and in C1 xlhelp's formula (=IF(B1>0,1+MOD(B1-1,9),0)).

combo

 

[mintjulep]

xlhelp's formula hurts my head.

I haven't figured out why it works yet.

 

[SPV]

In a nutshell, xlhelp's formula works because 10 mod 9 is 1.

In a bit more detail... Any number is just a sum of 1s, 10s, 100s etc. Any power of 10 is always 1 higher than a multiple of 9. That's the same as saying that [any power of 10] mod 9 is 1.

Taking the mod of the sum of two (or more) numbers is the same as taking the mod of each number then adding them (caveat: when you add the mods you might go above the base, but you just take the mod again). That's to say:
(a + b) mod 9 = (a mod 9 + b mod 9) mod 9

So with a bit of expanding out of brackets, which I won't put in this post, the original number mod 9 is just the sum of each digit.

Clear as mud? I thought so.

 

[SPV]

Forgot to say two things:

1. This is the basis for the classic tests for divisibility by 9. A number is is divisible by 9 if and only if the sum of its digits is divisible by 9. The same is true for divisibility by 3 (because 10 mod 3 is 1).

2. Sorry if eith of my posts causes more brain ache than xlhelp's very elegant formula.

spv

 

[gs99]

combo,
Thanks, your "two-step" code works.
I could not copy the code from your post. Had to type the formula into cell B1, then CTRL+SHIFT+ENT as you indicated.
The formula then appears within curly braces.
I can copy that formula into other cells.

A problem started to appear at number 74.

However, I noticed that my simple addition formula in number 73 does not work right to sum these values:
498,454,011,879,264
806,515,533,049,393
---------------------
1,304,969,544,928,657 is correct sum
1,304,969,544,928,660 is sum provided by my Excel XP 2002.

Your code provides correct "4" based on incorrect Excel sum. But the expected value is "1" based on correct sum.

Please compare my results with your Excel version (anybody).
Any comments why this happens?

 

[combo]

You reached the limit within which excel can handle integer numbers exactly and starts rounding.
You now need to work out your own math, maybe UDF adding numbers as strings (string+string=>string).

NB, for positive integers xlhelp's formula can be simplified to 1+MOD(A1-1,9).

combo

 

[kjv1611]

You reached the limit within which excel can handle integer numbers exactly and starts rounding.

If this is the case, would creating a custom function in VBA fix the problem? I am just curious. This is a rather interesting problem/solution.

--

"If to err is human, then I must be some kind of human!" -Me

 

[combo]

kjv, I proposed working with strings. A quick solution (UDF):

Public Function Add2Strings(Str1 As String, Str2 As String) As String
Dim MaxLength As Integer, MinLength As Integer, LenStr1 As Integer, LenStr2 As Integer
Dim MaxStrRev As String, MinStrRev As String
Dim CharSum As Integer
Dim bOverflow As Boolean
LenStr1 = Len(Str1)
LenStr2 = Len(Str2)
If LenStr2 > LenStr1 Then
    MaxLength = LenStr2: MinLength = LenStr1
    MaxStrRev = StrReverse(Str2): MinStrRev = StrReverse(Str1)
Else
    MaxLength = LenStr1: MinLength = LenStr2
    MaxStrRev = StrReverse(Str1): MinStrRev = StrReverse(Str2)
End If
Add2Strings = ""
bOverflow = False
For i = 1 To MinLength
    CharSum = CInt(Mid(MinStrRev, i, 1)) + CInt(Mid(MaxStrRev, i, 1))
    If bOverflow Then CharSum = CharSum + 1
    Add2Strings = Add2Strings & Right(CStr(CharSum), 1)
    If CharSum > 9 Then bOverflow = True Else bOverflow = False
Next i
For i = MinLength + 1 To MaxLength
    CharSum = CInt(Mid(MaxStrRev, i, 1))
    If bOverflow Then CharSum = CharSum + 1
    Add2Strings = Add2Strings & Right(CStr(CharSum), 1)
    If CharSum > 9 Then bOverflow = True Else bOverflow = False
Next i
If bOverflow Then Add2Strings = Add2Strings & "1"
Add2Strings = StrReverse(Add2Strings)
End Function

combo

 

[fneily]

See:
Sum-of-Digits Iterations

http://www.mathpages.com/home/kmath020.htm

Formula to Sum Digits of a Number(both positive and negative)

http://support.microsoft.com/kb/214053

And why doesn't the original mathematician who developed the proposed formula get any stars??

 

[Gavona]

Glenn: thank you for taking the time to explain that - I guess I should have looked up 'recursive' in the dictionary but it did not seem to be obvious from the op.

XLHELP: apologies for doubting your formula. I still need half an hour to get my head around it - or why on earth it is useful for that matter!


Gavin

 

[gs99]

I started the Office 2007 Online Trial. Excel 2007 adds my numbers in the same incorrect way: 1,304,969,544,928,660.
That is a problem.
Excel knows how to show error messages; why doesn't it tell me there is a problem?

>Gavin said "I still need half an hour to get my head around it - or why on earth it is useful for that matter!"

This is for learning the Fibonacci series; not sure what commercial value; someone else may "connect the dots". Perhaps some math or science teacher will appreciate this. I learned about the Fibonacci series in book "Sacred Geometry" by Stephen Skinner. The pattern can be seen in living things.

So I made a sheet to generate the series, in which the prior two numbers are added.
1, 2, 3, 5, 8, 13, 21 ... (very simple)

I then added a cell to divide each number by the prior number to see the golden mean (phi, 1.618).

Then at this site:

http://goldennumber.net/fibonacci24.htm

learned about the 24 pattern. This requires "numeric reduction" - adding the digits of each number. So my original post asked how to do this.

Thanks to all for your suggestions.

 

[gs99]

I made a VBA sub that calls the function Add2Strings.
(needed to add "Dim i as integer")
It seems to work OK; I stopped at number 96 that has 20 digits.
Number 73 sum is correct 1,304,969,544,928,657.

Now I need help to convert the formulas to VBA code.
Col B. (=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
Col C. (=IF(B1>0,1+MOD(B1-1,9),0))

Thanks

 

[combo]

Formula in col. B simply calculates sum of digits. In VBA it is much simpler:
For i=1 To Len(EntryString)
SumOfDigits=SumOfDigits+CInt(Mid(EntryString, i, 1))
Next i

You can do the whole task (cols B and C) in one function:
FinalSumOfDigits=1 + (SumOfDigits-1) Mod 9

In excel you can convert a standard to array formula (and back) by starting to edit it and entering either by single ENTER or CTRL+SHIFT+ENTER keys. Together with Add2Strings function, you can test all in excel sheet.

combo