Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Add/Sum Each Digit In Long Number

  1. #1
    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.

    Thanks a lot in advance for your help.
    Last edited by Cokie69; October 17th, 2006 at 09:30.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    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. #3
    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. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Adding The Digits In A Long Number

    Hi,

    Try,

    =1+MOD(A1-1,9)

    HTH

  5. #5
    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
    Regards

    Rich

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    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. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Adding The Digits In A Long Number

    Quote 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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Maximum Number With Certain 1st Digit
    By slyzenoui in forum Excel General
    Replies: 2
    Last Post: May 1st, 2008, 01:06
  2. Seperate 2 Digit Number Into 2 Single Digit Numbers
    By Triggman in forum Excel General
    Replies: 4
    Last Post: April 3rd, 2008, 11:59
  3. is a number or a digit
    By Richnl in forum Excel General
    Replies: 3
    Last Post: June 22nd, 2006, 17:27
  4. Replies: 5
    Last Post: November 26th, 2005, 02:07
  5. Changing two digit number
    By Mr_Thompson in forum Excel General
    Replies: 9
    Last Post: September 10th, 2004, 10:39

Bookmarks

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