Announcement

Collapse
No announcement yet.

Add/Sum Each Digit In Long Number

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 09:30.

  • #2
    Re: Adding The Digits In A Long Number

    Hi

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

    Code:
    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

    Comment


    • #3
      Re: Adding The Digits In A Long Number

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

      Comment


      • #4
        Re: Adding The Digits In A Long Number

        Hi,

        Try,

        =1+MOD(A1-1,9)

        HTH
        Kris

        ExcelFox

        Comment


        • #5
          Re: Adding The Digits In A Long Number

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

          Regards

          Rich
          Regards

          Rich

          Comment


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

            Comment


            • #7
              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
              Kris

              ExcelFox

              Comment

              Working...
              X