how do i add all digits in a cell with result being less than 10

[rufuxinix]

I need to add all digits in a cell and the result must be less thank 10
Example - 48 = 4+8 = 12 = 1+2 = 3

 

[Eric W]

Welcome to the forum. Try:

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

 

[Joe4]

[COLOR=#333333]=IF(A1=0,0,MOD(A1-1,9)+1)[/COLOR]

Brilliant, Eric!

So simple! I am curious as to the logic and mathematics behind it.
I can see what the formula is doing, but would you find explaining how you arrived at that?

 

[Eric W]

Well, how much do you remember about algebra, specifically modulus arithmetic?

Here's kind of a quick explanation, which assumes you remember a few things about the commutative (A+B=B+A), associative ( A+(B+C)=(A+B)+C), and distributive (A*(B+C)=A*B+A*C) properties.

First, the Modulus function gives you the remainder when you divide one number by another, MOD(15,9) = 6 because 15=9*1+6. The cool thing about the Mod function is that all of the usual properties still apply after you apply it. For example, 25+18=43. But also MOD(25,9)+MOD(18,9)=MOD(43,9)=>7+0=7. Also 23*17=391. Or MOD(23,9)*MOD(17,9)=MOD(391,9)=>5*8=4=>MOD(40,9)=4=>4=4. Try a few examples to see. This applies for any modulus, not just 9. I'm just using 9 for now since that's what the OP asked. This can be proven, but that's more than I have time for.

Once you accept that much, let's take an example of the number 234. Most of the steps below should be self-evident, but if something doesn't make sense, let me know.

234 =
200 + 30 + 4 =
2 * 100 + 3 * 10 + 4 * 1 =
2 * (99 + 1) + 3 * (9 + 1) + 4 * 1 =
(2 * 99 + 2 * 1) + (3 * 9 + 3 * 1) + (4 * 1) =
(2*99 + 2 ) + (3*9 + 3) + (4) =
2*99 + 3*9 + 2 + 3 + 4

Now we can apply the MOD function to the last line. The first 2 terms are evenly divisible by 9 so the remainder is zero, leaving 2+3+4, which is the sum of the digits. But applying the MOD function to the last line is the same as applying it directly to 234, since every step along the way is a valid transformation. Ergo, applying the Mod function directly to the number is the same as summing the digits. Also, if the sum of the digits is greater than 9, you can see that applying the process in an iterative fashion maintains the remainder each time.

The -1 / +1 trick is so that if you take MOD(9,9) you're not left with 0, but if you start with 0, that's what you get.


I hope this makes some kind of sense, since I wrote it up fairly quickly. Check Wikipedia for "Digital root" or "Casting out nines". Both of those have a somewhat more rigorous explanation, but also a bit longer. I'll give it another shot if there's something unclear. Hope this helps!

 

[Scott Huish]

If you wanted to allow for negative numbers as well and deal with the 0 problem.
Say -15 would be -6 then this would also work:
=(MOD(ABS(A1)-1,9)+1)*SIGN(A1)

Excel 2010
	A	B
1	0	0
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
9	8	8
10	9	9
11	10	1
12	11	2
13	12	3
14	13	4
15	14	5
16	15	6
17	16	7
18	17	8
19	18	9
20	19	1
21	20	2
22	-15	-6
Sheet1

Cell Formulas
Range	Formula
B1	=(MOD(ABS(A1)-1,9)+1)*SIGN(A1)
B2	=(MOD(ABS(A2)-1,9)+1)*SIGN(A2)
B3	=(MOD(ABS(A3)-1,9)+1)*SIGN(A3)
B4	=(MOD(ABS(A4)-1,9)+1)*SIGN(A4)
B5	=(MOD(ABS(A5)-1,9)+1)*SIGN(A5)
B6	=(MOD(ABS(A6)-1,9)+1)*SIGN(A6)
B7	=(MOD(ABS(A7)-1,9)+1)*SIGN(A7)
B8	=(MOD(ABS(A8)-1,9)+1)*SIGN(A8)
B9	=(MOD(ABS(A9)-1,9)+1)*SIGN(A9)
B10	=(MOD(ABS(A10)-1,9)+1)*SIGN(A10)
B11	=(MOD(ABS(A11)-1,9)+1)*SIGN(A11)
B12	=(MOD(ABS(A12)-1,9)+1)*SIGN(A12)
B13	=(MOD(ABS(A13)-1,9)+1)*SIGN(A13)
B14	=(MOD(ABS(A14)-1,9)+1)*SIGN(A14)
B15	=(MOD(ABS(A15)-1,9)+1)*SIGN(A15)
B16	=(MOD(ABS(A16)-1,9)+1)*SIGN(A16)
B17	=(MOD(ABS(A17)-1,9)+1)*SIGN(A17)
B18	=(MOD(ABS(A18)-1,9)+1)*SIGN(A18)
B19	=(MOD(ABS(A19)-1,9)+1)*SIGN(A19)
B20	=(MOD(ABS(A20)-1,9)+1)*SIGN(A20)
B21	=(MOD(ABS(A21)-1,9)+1)*SIGN(A21)
B22	=(MOD(ABS(A22)-1,9)+1)*SIGN(A22)

 

[Joe4]

Thanks. I was a Math major in college, so I am familiar with most of the rules you mentioned.
I think the "rule of nines" was the piece that I forgot about that allows you to do this.

 

[Eric W]

I also was a Math major in college, many years ago. I also like recreational mathematics, so a lot of those ancient rules tended to "stick" in my head.

Scott, while your formula works, I don't especially like adding a sign to the digital root. -6 mod 9 == 3 mod 9 (any two numbers that are different by a multiple of 9 are equivalent mod 9). However, if using the digital roots to perform a check on some calculation, I think it makes sense to make the check as easy as possible, meaning no signs.

 

[shg]

There was a bug in MOD described in MS KB119083, but that link has died. Allen Wyatt describes it at https://excel.tips.net/T003302_Large_Numbers_in_the_MOD_Function.html. I don't know if the bug is extant in later versions of Excel, but the fix is

=IF(A1=0, 0, A1 - FLOOR(A1-1, 9))

 

[Eric W]

I ran a few tests on Excel 365 and I didn't get the described error, so maybe it's fixed. Also, according to that link, using a modulus of 9, the error should only occur for values over 1,207,959,552.

 

[rufuxinix]

Welcome to the forum. Try:

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

Thanks, Eric! Works like a bomb, simple n straight forward