Welcome Guest
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
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?

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

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).
• 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!!

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

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.

 Page 1 of 1