Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
17th October 2006
Posts
2

## Add/Sum Each Digit In Long Number

Hi there,

Here I have a simple problem, but no clue how to go forward in Excel...

I have a cell with a number which is a result of several previous sums. It's a number with 8 to 11 digits. For example: 878567663.

I need to add each digit of the number, and then add again the result until I end up with a single digit. In the above example it'd be (8+7+8+5+6+7+6+6+3=56), then (5+6=11), then (1+1=2) and put the end result (2) in a cell

My main doubt is that I don't know how to use a simple "do while" type structure in Excel to achieve the above.

Questions:

1.) How do I create a formula to do that?. I have seen solutions in this board that let me add each digit but that's it... I need to "reduce" it until only one digit is left...
2.) What about if the lenght of the number is not always the same (fe. in one cell is 8, in another is 11, the next one is again 8...)?

I am not sure if my explanations are clear enough... please let me know.

Last edited by Cokie69; October 17th, 2006 at 09:30.

Excel Video Tutorials / Excel Dashboards Reports

2. Long Term Member
Join Date
6th May 2005
Posts
1,036

## Re: Adding The Digits In A Long Number

Hi

Here's one way. You will have to create your own function (UDF).

VB:
```Function MySum(ra As Range)
resultt = ra.Value
Do
holder = 0
For i = 1 To Len(resultt)
holder = holder + Val(Mid(resultt, i, 1))
Next i
resultt = holder

Loop Until Len(resultt) = 1

MySum = resultt
End Function

```
Put this code in a general module in your workbook.

If your source data is in A1 then use the function in the form

=mysum(a1)

HTH

Tony

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
17th October 2006
Posts
2

## Re: Adding The Digits In A Long Number

Excellent Tony,
I'm gonna do that and see what happens... thanks a lot!.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Adding The Digits In A Long Number

Hi,

Try,

=1+MOD(A1-1,9)

HTH

5. Senior Member
Join Date
14th July 2005
Location
On the Wirral
Posts
196

## Re: Adding The Digits In A Long Number

Have a look at this: http://support.microsoft.com/kb/214053

Regards

Rich

6. ## Re: Adding The Digits In A Long Number

Brilliant Kris!

I hate to say anything but I think it needs to be =1+MOD(A1-1,LEN(A1)) to account for varying number lengths.

7. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Adding The Digits In A Long Number

Originally Posted by Dave Hawley
Brilliant Kris!

I hate to say anything but I think it needs to be =1+MOD(A1-1,LEN(A1)) to account for varying number lengths.
Hi Dave,

Credit goes to Harlan Grove

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno