Welcome Guest
Log In | Register )
You last visited December 3, 2016, 4:45 am
All times shown are
Eastern Time (GMT-5:00)

Excel question - I'm stuck

Topic closed. 6 replies. Last post 8 years ago by Bandito.

Page 1 of 1
PrintE-mailLink
Avatar
New Member

United States
Member #62904
July 12, 2008
5 Posts
Offline
Posted: July 12, 2008, 9:55 pm - IP Logged

Hey everyone,

Newbie here to the forum.. I am stuck on a Excel problem.   I have read the posts and see that Rakster has some similar questions on this topic, I tried the suggestions he was offered.. they work to a degree but not to the level I need.. here is my problem:

 

I would like to add the digits of a two or three digit number within a cell... for example:

a two digit number say 94 in A1..  those two digits added together, 9+4=13.   I would like to further add

13, 1+3=4..   Same for a three or more digit number...  185 would be 1+8+5=14,  taking it further, 1+4=5.

Can someone come up with a single formula which can do this?   Many thanks in advance for your help..

    winsumloosesum's avatar - Lottery-060.jpg
    Pennsylvania
    United States
    Member #2218
    September 1, 2003
    5387 Posts
    Offline
    Posted: July 12, 2008, 11:40 pm - IP Logged

    Hey everyone,

    Newbie here to the forum.. I am stuck on a Excel problem.   I have read the posts and see that Rakster has some similar questions on this topic, I tried the suggestions he was offered.. they work to a degree but not to the level I need.. here is my problem:

     

    I would like to add the digits of a two or three digit number within a cell... for example:

    a two digit number say 94 in A1..  those two digits added together, 9+4=13.   I would like to further add

    13, 1+3=4..   Same for a three or more digit number...  185 would be 1+8+5=14,  taking it further, 1+4=5.

    Can someone come up with a single formula which can do this?   Many thanks in advance for your help..

    Bandito,

    Try this:

    Open Excel

    • Press Alt+F11 to open Visual Basic Editor (VBE).
    • From the Insert menu, select Module (to add a module).
    • Copy and Paste the following lines of code into the module (Blank box on the right side):


     Function SumDigits(Number)
        Dim i As Integer
        For i = 1 To Len(Number)   
          SumDigits = SumDigits + Val(Mid(Number, i, 1))
        Next i
     End Function

     

    Close the Visual Basic Editor.

    To test the function:

    • To test the function, open Paste Function (Shift+F3).
    • From the User Defined category (use the drop-down arrow), select the "SumDigits" function.
    • In the function box, select any cell that contains a number.
    • Click OK.
    • Good for Pick 3 and Pick 4 digit numbers or any size number

    So whatever number is in the cell this new formula will add the number in the cell you are formulating.

     

    Here is a link to download the Excel File:

    http://www.badongo.com/file/10361101

      Avatar
      New Member

      United States
      Member #62904
      July 12, 2008
      5 Posts
      Offline
      Posted: July 12, 2008, 11:53 pm - IP Logged

      Thanks winsumloosesum, that works but it dosen't do exactly what I'm looking for..

      for example, I enter the VB formula (Sumdigits) into my spreadsheet and it adds the digits

      in the cell.   Say I have 95 in A1.. the formula does return 14.  But I'm looking for a formula which would

      return 5...  9+5=14, then adding 1+4 to get 5.  Looking for a single formula which would do this???

       

      Thanks,

      Bandito.

        Avatar
        New Member
        tacoma
        United States
        Member #35425
        March 17, 2006
        1 Posts
        Offline
        Posted: July 13, 2008, 1:36 am - IP Logged

        click on cell you want total value in and enter this command =sum(9,5) for every additional number in cell just add a comma between them.

          Avatar
          New Mexico
          United States
          Member #58526
          February 18, 2008
          683 Posts
          Offline
          Posted: July 13, 2008, 1:54 am - IP Logged

          Just use any of the above suggestions to create a total for cell A1 in cell B1, then use it again to total cell B1 and put that in cell C1 (or whatever format works for you)  If you don't want to see the intermediate values you can hide those columns.

            time*treat's avatar - radar

            United States
            Member #13130
            March 30, 2005
            2171 Posts
            Offline
            Posted: July 13, 2008, 4:55 am - IP Logged

            ...slight variation of WSLS's work (this is good unless you're adding the digits of excessively large numbers...)

             Function SumDigits(Number)
                Dim i As Integer, fake__sum As Integer
               
                While Number > 9
                   fake__sum = 0
                   For i = 1 To Len(Number)
                      fake__sum = fake__sum + Val(Mid(Number, i, 1))
                   Next i
                   Number = fake__sum
                Wend
                SumDigits = fake__sum
             End Function

            to use, type in =SumDigits(number) in any cell, or call from other precedures. Cool

            In neo-conned Amerika, bank robs you.
            Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

              Avatar
              New Member

              United States
              Member #62904
              July 12, 2008
              5 Posts
              Offline
              Posted: July 13, 2008, 10:45 am - IP Logged

              Thanks time*treat, that did it...  and thanks to everyone who offered help.. appreciate it.