[Solved] Calculating digit sum of integers in a column

[Brokenfingers]

Hello, fresh spawn first post.
I have been snoopin' around for a peculiar formulae. I wish to have my PC calculate numerous ammounts of numbers, and eventually refine the sum numbers to but a single integer, by means of cascading calculations.
However, i do not posses profficient knowledge regarding calc. nor am i particularily proned to learn. In other terms, i cant be bothered to learn. Also- youtube help vids will notnot suffice.
Anyways....

Example:

133 1+3+3=7 Digit Sum =7

777 7+7+7 = 21

124875 1+2+4+8+7+5 = 27 (FYI: since the digit sum of 124875 consists of more than one integer, the next step will be to calculate the digit sum of 27. i.e. 2+7=9. the final result may never exceed "9" 27>9)



I found this little snippet of code but i am having problems implementing it in my calc sheet.
halp?

Code: Select all

=IF(MOD(Cell Number,9)=0,"9",MOD(Cell Number,9))

Hope for the best

[acknak]

Greetings and welcome to the community forum!

Here's one way to add up all the digits of a number in cell A1:
=SUMPRODUCT(VALUE(MID(A1;ROW(INDIRECT("z1:z"&LEN(A1)));1)))

[Brokenfingers]

that computes to #NAME?

[acknak]

Try this one

[robleyd]

Perhaps you have entered it incorrectly? It works for me in the attached example.

You may need to process your initial number several times to get it to a single digit if that is what you want. You would need additional cells to process the previous result if it is greater that 9, that is, more than a single digit. See the IF() function in the help for more info.

[Lupp]

To get the iterated sum of digits the solution already mentioned by acknak is by far the best one. You may modify it to the simple:

Code: Select all

=MOD(A1;9)

if you are ready to accept the (relevant) result 0 instead of 9 in specific cases. The variant

Code: Select all

=MOD(A1;9)+IF(CURRENT()=0;9;0)

will avoid the zeros.

Please note that the non-iterated sum of digits calculated by the formula acknak also suggested actually needs to first convert the number to text. It will do so automatically but you may prefer to be explicit about this:

Code: Select all

=SUMPRODUCT(VALUE(MID(TEXT(A1;"0");ROW(INDIRECT("z1:z"&LEN(TEXT(A1;"0"))));1)))

(The iterated sum of digits is what we use when deciding about divisibility by 3 or by 9. The MOD function is made for getting the remainder of any integer division. Its uage with non-integers needs a bit more of studies.)

[Brokenfingers]

So now i struck thios error

[JohnSUN-Pensioner]

I think you want to get something like this...

[Lupp]

@Brokenfingers: Sheet1.A80:A82 of your recent example don't contain integers.

The concept of "sum of digits" is not applicable to arbitrary numbers in arbitrary notations.
To calculate sums of that order of magnitude to the full precision you would need "Arbitrary Precision Arithmetics". ("JohnSUN-Pensioner" already gave a limited example for it.) Doing this to the full extent is a domain of special Matematics Software. A commercial example: Mathematica (Wolfram); a free example: Maxima (derived from former US government's Macsyma; kind of donation; thanks!).

Concerning the iterated sum of digits you don't need to calculate the elements of your sequence at all.

Why that's the case?
The two-step recursion of the Fibonacci-sequence is also valid for calculations modulo any specific divisor. The results should be interpreted as representants of the respective residue classes. As always you may choose the number 9 to represent the class of "no residue under division by 9" instead of the more common 0.

Your column, say Z - and no label assumed, for the iterated sums of digits may contain in its rows number 1 and number 2 the values 1 and 1 again. Starting with its third row you may use

Code: Select all

=MOD(Z1 + Z2 ; 9) (... or ...) =MOD(Z1 + Z2 ; 9) + IF(CURRENT() = 0 ; 9 ; 0)

and its filled down adaptions. The first variant if you accept the 0 for zero, the other one if you prefer 9 to represent the zero class.

[John_Ha]

For others searching ...

You are calculating the digital root of the number.

The simplest formula uses the floor function (see Calc floor) and is given by

Edit: An even easier formula for the digital root is Code: Select all =IF(MOD(A5,9)=0,9,MOD(A5,9)) where MOD finds the remainder when the number in A5 is divided by 9. If the remainder is 0 the result is set to 9.

[karolus]

Hallo

Why not invert the condition:

Code: Select all

=IF( MOD( A5;9 ) ; MOD( A5;9 ) ; 9 ) 

or with CURRENT…

Code: Select all

=MOD( A5;9 ) + NOT(CURRENT())* 9

[MrProgrammer]

I wish to have my PC calculate numerous ammounts of numbers, and eventually refine the sum numbers to but a single integer, by means of cascading calculations.

This vague statement refers to the calculation of the Digital Root, used in Western numerology, but without much importance in proper mathematics. Brokenfingers has two major problems with the quest:
• The showstopper is: "i cant be bothered to learn". Apparently correct English isn't of interest either.
• Another important one is the OP's blissful ignorance of the precision limits in spreadsheets.
The latter can be circumvented, as shown in [Solved] Problem creating the Fibonacci series which performs calculations with 28 digits of precision, but not by someone who wants to remain uneducated.

If the goal is to calculate the digital roots of the Fibonacci numbers, they form a repeating pattern of 24 numbers beginning with these roots for F₁, F₂, F₃, …:
1, 1, 2, 3, 5, 8, 4, 3, 7, 1, 8, 9,
8, 8, 7, 6, 4, 1, 5, 6, 2, 8, 1, 9.
The pattern then repeats for F₂₅, F₂₆, F₂₇, …, F₄₉, F₅₀, F₅₁, …. If the term of the pattern is not 9, it and the term 12 below it sum to 9.

=IF(MOD(n;9)=0;9;MOD(n;9))

=IF(MOD(n;9);MOD(n;9);9)

Another simple formula is =MOD(n-1;9)+1.

Sheet1.A80:A82 of your recent example don't contain [the correct] integers.

Cell A75 is supposed to be F₇₄ (which should be F₇₂+F₇₃ = 498454011879264+806515533049393) but the sum exceeds 15 significant digits and it is clear the value 1304969544928660 is not the next Fibonacci number since it should end in 7 but ends in 0. All successive values in column A are also incorrect. However since =MOD(74;24) is 2, the digital root of F₇₄ is the same as that of F₂ which is 1, given above.

The results should be interpreted as representants of the respective residue classes.

Agreed. The residue class method is best as long as the values are integers with no more than 15 digits. If (and only if) they are integers stored as text, I use this formula:
=SUMPRODUCT({1;2;3;4;5;6;7;8;9}*(LEN(n)-LEN(SUBSTITUTE(n;{1;2;3;4;5;6;7;8;9};""))))

This attachment shows calculation (without macros) of the digital sum and the iterated sum for values of lengths 1 through 20 and then for a value with 135 digits. This set of values is taken from the decimal expansion of π, not from the Fibonacci series.

[keme]

[...] However, i do not posses profficient knowledge regarding calc. nor am i particularily proned to learn. In other terms, i cant be bothered to learn. [...]

Then just pick any of the formulas given as suggestion. As far as I can see, they will all arrive at the correct solution, albeit in some cases requiring multiple iterations.

Note that the SUMPRODUCT() suggestions will use the sequential approach you envisioned. The mathemathical approach using MOD(9) relies on a pecularity of the decimal number system. If you feel (as some numerologists do) that there is some magic in the sequential method, by all means shy clear of any formula using MOD()

By far the simplest solution (which would be my choice) is MrProgrammer's =MOD(n-1;9)+1. This (and other formulas using the mathemathical approach) will work on all natural numbers (integers >0) with up to 14 digits.

If you have more digits, Calc will not be able to represent the digit sequence as an exact number. The remainder trick (MOD() ) will not work with non-integer, zero or negative numbers. Use one of the other approaches.

Also note that suggestions using comma for separator will not work with OpenOffice Calc. Use semicolon instead.

[Lupp]

Just for fun I made an example creating sequences (up to 200 digits) of decimal numbers as texts. The generator, however, uses the TEXTJOIN() function only available in a debugged version in LibreOffice 4.4 or higher.
The example also contains a SUMPRODUCT() formula calculating the digitsum for long textual integers without constant arrays.

The main concern is the demonstartion of the equivalence of the iterated proceeding and the usage of MOD() approaches where applicable. (Not a proof, a demo. Users interested in proofs will know anyway.)
To make this part also working for users of AOO, I made a copy of the generating sheet having pasted in an example of the long integres as constants.