Add digits together

[S.H.A.D.O.]

Good afternoon,

The code below adds together ALL the individual digits of each combination and lists those totals, it also lists the total combinations associated with each of those totals.

The Excel formula if the combination was in cells A1:F1 would be...

=SUMPRODUCT(INT(A1:F1/10)+MOD(A1:F1,10))

My question is, how can I adapt this code to add those totals together and list the total combinations associated with each of those totals.

For example, if the combination was 05, 44, 46, 48, 50, 52, then 5+4+4+4+6+4+8+5+5+2 = 47, and 47 would be 4+7 = 11.

I know that the minimum would be 2 and the maximum would be 15.

The Excel formula if the combination total was in cell A1 would be...

=INT(A1/10+MOD(A1,10))

I just can't seem to incorporate this into the existing code and get it to work.

Here is the code...

Option Explicit
Option Base 1
Const MinA As Integer = 1
Const MaxF As Integer = 59
    
Sub Root_Sum()
    Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
    Dim Num As Long
    Dim nums(59) As Long
    Dim Root As Long
    Dim Map(11 To 76) As Double
    Dim n As Long
    Dim Total As Long
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Columns("A:B").ClearContents
    For Num = LBound(nums) To UBound(nums)
        nums(Num) = Num \ 10 + Num Mod 10
    Next Num
    For A = MinA To MaxF - 5
        For B = A + 1 To MaxF - 4
            For C = B + 1 To MaxF - 3
                For D = C + 1 To MaxF - 2
                    For E = D + 1 To MaxF - 1
                        For F = E + 1 To MaxF
                            Root = nums(A) + nums(B) + nums(C) + _
                                   nums(D) + nums(E) + nums(F)
                            Map(Root) = Map(Root) + 1
                        Next F
                    Next E
                Next D
            Next C
        Next B
    Next A
    For n = LBound(Map) To UBound(Map)
        Total = Total + Map(n)
        ActiveCell.Offset(n - LBound(Map), 0).Value = n
        ActiveCell.Offset(n - LBound(Map), 1).Value = Map(n)
    Next n
    ActiveCell.Offset(n - LBound(Map), 1).Value = Total
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Thanks very much in advance.

 

[igold]

Hi,

I have a question.. In your example above:

the combination was 05, 44, 46, 48, 50, 52, then 5+4+4+4+6+4+8+5+5+2 = 47, and 47 would be 4+7 = 11.

you stop at 11. Is there a reason why you do not take it to the next step which would be adding 1+1 to get a final answer of 2. Way back, I learned this process and it was called "Casting out Nines". It is a very useful in finding accounting errors especially transposition errors and checking accuracy of calculations in general. I ask because that formula is much cleaner as:

=MOD(A1,9)

In the process of Casting Out Nines you just keep adding the individual digits together and every time the sum is two digits (greater than 9), you add the two digits together and then move on to the next if the sum equals 9 start from 0. In your example if I were to do it in my head it would go something like this- your digits are in red:

5+4 =9 (since it is 9 it is like starting over at zero) 4+4=8 +6=14 (add 1+4 =5) +4=9 (its a nine, go to 0) 8+5=13 (add 1+3=4) +5=9 (its a nine, go to 0) +2 = 2. Which would be your answer had you added the two digits of 11 together.

I have not looked hard at your code, I was more trying to get an idea of what your trying to accomplish by finding the sum of the digits, and also perhaps provoke some thought to the logic behind your code.

Just curious and some food for thought...

igold

 

[S.H.A.D.O.]

Thanks for the reply igold, it is appreciated.

I am interested in the statistical analysis aspect, this comes in three parts.

Part 1:
The code in my initial post achieves the first part, which is to add together ALL the individual digits of each combination and list those totals, along with the total combinations associated with each of those totals.
This part is done.

Part 3:
I already have the code which adds together ALL the individual digits of each combination, and then keeps on adding the digits together until we arrive at a single digit from 1 to 9, along with the total combinations associated with each of those totals.
This part is done.

Part 2:
This is the bit I am stuck on, it is the intermediate stage of Part 1 & Part 3 that I am having trouble with.
As I have stated previously, I know that the minimum to this solution will be 2 and the maximum would be 15, obviously with the total combinations associated with each of those totals.

Thanks in advance.

 

[Ron Coderre]

By any chance, are you just trying to calculate a check-digit?

If yes, please post some sample values and the check-digit that would result from those samples.

 

[S.H.A.D.O.]

By any chance, are you just trying to calculate a check-digit?

Thanks for the reply Ron,

No, it doesn't have anytning to do with calculating a check-digit. It is purley for a statistical analysis that I am doing with regard to the lottery.
As I said previously, there are three parts to what I am trying to achieve, two of which I have achieved already, it is just the Part 3 I am having difficulty with.

Thanks again for your interest.

 

[JackDanIce]

SHADO, I suspect there is confusion between what you're explaining and what you're seeing on your monitor as the replies seem to indicate that. Can you post a "before" and "after" example pls?

I understand how the maximum is 15 (using values of 54-59 inclusive), but I can't determine the minimum of 2 (if I use values 1-6 inclusive, 1 being your min value, based on your code; I get an answer of 3, based on the same process to derive 15)

I also suspect there is an alternative rather than using so many nested loops.

 

[S.H.A.D.O.]

I understand how the maximum is 15 (using values of 54-59 inclusive), but I can't determine the minimum of 2 (if I use values 1-6 inclusive, 1 being your min value, based on your code; I get an answer of 3, based on the same process to derive 15)

Thanks for the reply Jack,

You are correct, if we used:

01-02-03-04-05-06, then 0+1+0+2+0+3+0+4+0+5+0+6 = 21, therefore 2+1 = 3

01-02-03-10-11-20, then 0+1+0+2+0+3+1+0+1+1+2+0 = 11 therefore 1+1 = 2 <---Minimum value

54-55-56-57-58-59, then 5+4+5+5+5+6+5+7+5+8+5+9 = 69 therefore 6+9 = 15 <---Maximum value

I hope this makes sense!

 

[JackDanIce]

Understand, didn't realise a combination existed that returns 2. However, you still haven't given an example of "before" and "after", I think it would really help to clarify your requirement...

Explicity state, some example values in cells A1:F1 and what cell address should contain the output and what that output should be.

E.g.

A1:F1 contains 10, 12, 13, 14, 15, 16

G1 should contain value 8, based on the sum of the individual digits. If the sum of the individual digits is greater than 9 (e.g. 10, 11, .... 69) then sum the individual digits again

 

[S.H.A.D.O.]

I think I've worked it out. I know I just needed to add...

=INT(existing total / 10 + MOD (existing total, 10))

...to get the digits to again add themselves together.

I replaced...

Map(Root) = Map(Root) + 1

...with...

Map(Root \ 10 + Root Mod 10) = Map(Root \ 10 + Root Mod 10) + 1

...along with changing the...

Dim Map(11 To 76) As Double

...to...

Dim Map(2 To 15) As Double

...and it seems to have done the trick.

Thanks to everyone who took the time to look and reply to this, it is appreciated.

 

[S.H.A.D.O.]

Thanks Jack for your time and input on this.

In fact, all the calculations are calculated and processed within the code itself, as opposed to obtaining the information from the Worksheet, it only outputs to the Worksheet once all the calculations have been processed.