Last Digit Distribution

[PAB]

Hi GillesD,

I know that we could use MOD 10 to Calculate the Last Digit or something like :-
LastDigit = A - 10 * Int(A / 10)

I think the code below would be a start :-

Option Explicit
Option Base 1

Sub Last_Digits()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim nVal(10) As Double
Dim i As Integer

Application.ScreenUpdating = False

For i = 1 To 10
  nVal(i) = 0
Next i

For A = 1 To 44
  For B = A + 1 To 45
    For C = B + 1 To 46
      For D = C + 1 To 47
        For E = D + 1 To 48
          For F = E + 1 To 49
          
            If nVal = 111111 Then nVal(1) = nVal(1) + 1
            If nVal = 211110 Then nVal(2) = nVal(2) + 1
            If nVal = 221100 Then nVal(3) = nVal(3) + 1
            If nVal = 222000 Then nVal(4) = nVal(4) + 1
            If nVal = 311100 Then nVal(5) = nVal(5) + 1
            If nVal = 321000 Then nVal(6) = nVal(6) + 1
            If nVal = 330000 Then nVal(7) = nVal(7) + 1
            If nVal = 411000 Then nVal(8) = nVal(8) + 1
            If nVal = 420000 Then nVal(9) = nVal(9) + 1
            If nVal = 510000 Then nVal(10) = nVal(10) + 1

          Next F
        Next E
      Next D
    Next C
  Next B
Next A

Range("B1").Select
For i = 1 To 10
  ActiveCell.Offset(i, 0).Value = nVal(i)
Next i

End Sub

The main problem is going to be if there is MORE than one Last Digit in a Combination.
We could use the "Concatenate" Function (&) to string them together and then use the "Large" Function to sort the highest from left to right.
Just a few ideas.

All the Best
PAB

 

[GillesD]

Last digit distribution

PAB, here is how I do it in a regular spreadsheet:

- in columns C to H, I have the 6 winning numbers;

- in columns K to P, I extract the last digit for each winning number using the MOD function;

- in columns Q to Z, I determine the number of 0 as last digit, then the number of 1 as last digit. ... up to the number of 9 as last digit using the COUNTIF function;

- in column AA, I calculate the last digit distribution using the LARGE function (basically the formula is something like: =LARGE(Qx:Zx;6)*1 +LARGE(Qx:Zx;5)*10+LARGE(Qx:Zx;4)*100 +LARGE(QX:Zx;3)*1000 +LARGE(Qx:Zx;2)*10000 +LARGE(Qx:Zx;1)*100000 where x is the line number);
Note: there should be no spaces in the formula such as ... 100 +LARGE...

- finally, using the COUNTIF function, I calculate the number of times each of the 10 possible distributions (from 111111 to 510000) occur in all draws.

This may not be the most elegant way to get the results wanted and if I had to redo the exercise now with a greater experience in Excel, I could probably do better. But as the saying goes, if it works, do not change anything.

I will try to look how this could be translated into a macro.

 

[PAB]

Thanks for the reply GillesD,

For interest, in a spreadsheet, this is the way I calculate the Last Digits and distribution.
To calculate the Last Digits for the 6 numbers drawn I use ...

=SUMPRODUCT(--(RIGHT($O117:$T117,1)="0"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="1"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="2"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="3"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="4"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="5"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="6"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="7"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="8"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="9"))

... I use ...

=SUM(DO117X117)

... to check the total was 6 (for 6 numbers drawn), I use ...

=CONCATENATE(DO117,DP117,DQ117,DR117,DS117,DT117,DU117,DV117,DW117,DX117)

... to string them together (I do this in case I want to extract specific information for any of the Last Digit data), and I use ...

=LARGE(DO117X117,1)*100000+LARGE(DO117X117,2)*10000+LARGE(DO117X117,3*)*1000+LARGE(DO117X117,4)*100+LARGE(DO117X117,5)*10+LARGE(DO117X117,6*)

... to transfer them into the distribution category.

I will try to look how this could be translated into a macro.

It will be nice to have a macro to do this as the logic can then be used for other calculations, such as First Digit etc.

All the Best.
PAB

 

[PAB]

Hi GillesD,

I have had a think about a solution for Last Digits. I think something like ...

Dim LastDigits(6) As Long

For A = 1 To 44
  LastDigits(1) = A Mod 10
  For B = A + 1 To 45
    LastDigits(2) = B Mod 10
    For C = B + 1 To 46
      LastDigits(3) = C Mod 10
      For D = C + 1 To 47
        LastDigits(4) = D Mod 10
        For E = D + 1 To 48
          LastDigits(5) = E Mod 10
          For F = E + 1 To 49
            LastDigits(6) = F Mod 10

... is the way I am approaching it.
I am getting there and will have another look at it again later in the week.

Thanks in Advance.
All the Best.
PAB

 

[therealpoorboy]

Hey guys

What are we trying to figuure out here:

The distribution of LDs (last digits) in set of drawn numbers or
you are trying to filter in specific LDs for set of combos?

I may be able to help out

Therealpoorboy