Welcome Guest
Log In | Register )
You last visited January 23, 2017, 5:52 pm
All times shown are
Eastern Time (GMT-5:00)

Root Sum Excel... Need some Help

Topic closed. 3 replies. Last post 10 years ago by budward.

Page 1 of 1
PrintE-mailLink
Avatar
Myrtle Beach, SC
United States
Member #18457
July 12, 2005
3433 Posts
Offline
Posted: November 8, 2006, 10:53 pm - IP Logged

LP members I need some help, is there anyone who has the spreadsheet formula when you enter a pick 3 combination into input, will give you the Root sum in the output cell?

Thanks in advance,

pcnut

"Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it."

                                                                                                                        David Starr Jordan

S.C.'s weekly-indicative random pairs: 05, 12, 13, 16, 18, 23, 34

Enjoy

pcnut

    winsumloosesum's avatar - Lottery-060.jpg
    Pennsylvania
    United States
    Member #2218
    September 1, 2003
    5396 Posts
    Offline
    Posted: November 8, 2006, 11:17 pm - IP Logged

    PC Nut

    Do the following:

    • Press Alt+F11 to open Visual Basic Editor (VBE).
    • From the Insert menu, select Module (to add a module).
    • Enter the following lines of code into the module:


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

    To test the function:

    • To test the function, open Paste Function (Shift+F3).
    • From the User Defined category, select the SumDigits function.
    • In the function box, select any cell that contains a number.
    • Click OK.
    • Good for Pick 3


    1.  If your 3 digit combination in cell B1

    2.  In Cell C1 type the following: =sumdigits(B1)

        This will give you the sum of the 3 digit combination

    3.  Create a New worksheet (Tab) Name it RootSums

    4. In cells A1 through A28 type in 0 through 27

    5. In  cells B1 through B28 enter the corresponding Root Sums from A1 through A28

    6. Go back to the Tab where you have your Pick 3 combination and Sum

    7. In cell D1 enter or copy the following formula:

    =LOOKUP(B1,RootSums!$A$1:$A$28,RootSums!$B$1:$B$28) 

    8. This will give you the Root Sum of the Sum

    9.  Drag down the formula for every entry you have.

    Good Luck!! 

      Avatar
      Myrtle Beach, SC
      United States
      Member #18457
      July 12, 2005
      3433 Posts
      Offline
      Posted: November 8, 2006, 11:50 pm - IP Logged

      Thank you winsumloosesum!

      I'm up and running, I appreciate your time and help....

      Thanks again.

      "Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it."

                                                                                                                              David Starr Jordan

      S.C.'s weekly-indicative random pairs: 05, 12, 13, 16, 18, 23, 34

      Enjoy

      pcnut

        Avatar

        United States
        Member #33295
        February 19, 2006
        699 Posts
        Offline
        Posted: November 9, 2006, 12:07 am - IP Logged

        This works also. Just replace A1 with what ever cell you are using.

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

        Isabel, you are going to feel very silly when this turns out to be make-believe.