Root Sum Excel... Need some HelpPrev TopicNext Topic

• pcnut

  

  Myrtle Beach, SC
  United States
  Member #18,456
  July 12, 2005
  3,536 Posts
  Offline
  Nov 8, 2006, 10:53 pm

  ▲▼

  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

  "If you embrace tenacity within your life, the end result will be your success."  

                                                                                                               Charles Byrd Rhue

  Enjoy

  pcnut
• winsumloosesum

  

  Philadelpia/Pennsylvania
  United States
  Member #2,218
  September 1, 2003
  6,802 Posts
  Online
  Nov 8, 2006, 11:17 pm

  ▲▼

  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!! 
• pcnut

  

  Myrtle Beach, SC
  United States
  Member #18,456
  July 12, 2005
  3,536 Posts
  Offline
  Nov 8, 2006, 11:50 pm

  ▲▼

  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

  "If you embrace tenacity within your life, the end result will be your success."  

                                                                                                               Charles Byrd Rhue

  Enjoy

  pcnut
• budward

  

  United States
  Member #33,294
  February 19, 2006
  699 Posts
  Offline
  Nov 9, 2006, 12:07 am

  ▲▼

  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.