Sum Count of identical Rightmost digits

[Sammy]

Hi Lotto Forum,

Can anyone help me with an Excel Formula:

I have a table of 5 columns (C to G) and many rows. Is there a way to sum the count of identical occurrences of Rightmost digits in a row, there may be one or two digits per cell. Results housed in column H - relevant row.

Example Data: (Columns C to G)
C D E F G
Row50 9 19 23 33 34
Row51 5 15 25 30 33
Row52 8 18 19 28 29
Row53 9 19 20 30 32
Row54 2 12 22 30 32

In Row50 the digit 9 appears twice in the rightmost position and the digit 3 also appears twice in the rightmost position in the cells. This gives a total summed count of 4.

Expected Results: (Column H)
Row50 4 - two matches of the digit 9 and digit 3 respectively.
Row51 3
Row52 5
Row53 4
Row54 4

There will be rows that have a zero count of identical Rightmost digits.

Assistance very much appreciated.

Cheers
Sammy

 

[GillesD]

Occurences of duplicate last digit

Nice question, Sammy

I have not found a formula (yet) giving the answer to your question, but a quick and not very elegant macro appears to provide acceptable answers. Here is the code:

Sub Test_Dupl()
Dim I As Integer, J As Integer, nDupl As Integer, nNum(5) As Integer
Do While ActiveCell <> ""
nDupl = 0
For I = 1 To 5
nNum(I) = ActiveCell.Offset(0, I - 1).Value
Do Until nNum(I) < 10
nNum(I) = nNum(I) - 10
Loop
Next I
For I = 1 To 4
For J = I + 1 To 5
If nNum(I) = nNum(J) Then nDupl = nDupl + 1
Next J
Next I
Select Case nDupl
Case 1
nDupl = 2
Case 2
nDupl = 4
Case 4
nDupl = 5
Case 6
nDupl = 4
Case 10
nDupl = 5
End Select
ActiveCell.Offset(0, 5).Value = nDupl
ActiveCell.Offset(1, 0).Select
Loop
End Sub

It works (as far I could test it) but the calculation of the nDupl and its eventual correction (with the Select Case statements) are not very good programming. I will try to generate a better code.

When starting the macro, the cursor should be in column C on the first row you want calculations to begin with. The answers will be placed in column H of each row. The macro will run until it finds values in column C.

 

[Sammy]

Hi GillesD,

Thank you for taking the time to provide me with a macro. It is doing the task very well....Brilliant!

Rather than placing the totals in column H, as I said previously, I would like to place them in column M - what will I need to amend in the macro?

Would still be very much appreciated if you are able to formulate a formula to do the job.

Cheers,
Sammy

 

[GillesD]

Macro change

To place the result in column M instead of H, just change the line ActiveCell.Offset(0, 5).Value = nDupl to ActiveCell.Offset(0, 10).Value = nDupl

And about a formula to calculate the same thing, it could be possible and would require a rather long formula, I think. Right now, I do not have any idea on how to formulate this. But may be in the near future, the idea will come (hey, this is the same thing I say about winning the lottery).

 

[Sammy]

Re: Macro change

Hi GillesD,

Thank you very much for all your help... greatly appreciated.

QUOTE]Originally posted by GillesD
And about a formula to calculate the same thing, it could be possible and would require a rather long formula, I think. Right now, I do not have any idea on how to formulate this. But may be in the near future, the idea will come (hey, this is the same thing I say about winning the lottery). [/QUOTE]

Let's hope the latter is sooner rather than later for both of us!

Cheers,
Sammy

 

[PAB]

Hi Sammy,

Assuming that the First Five Numbers are in Cells C16:G16 for Example, you could Put the Following Formulas in Cells I16:R16 and Copy Down. These will give you the TOTAL for EACH of the LAST DIGITS for EACH ROW. You could then Perform whatever Analysis that you want.

In Cell I16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="0"))
In Cell J16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="1"))
In Cell K16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="2"))
In Cell L16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="3"))
In Cell M16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="4"))
In Cell N16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="5"))
In Cell O16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="6"))
In Cell P16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="7"))
In Cell Q16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="8"))
In Cell R16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="9"))

This Formula Should Produce a Total of Five for Each of the Rows.
In Cell S16 Enter the Formula :-
=SUM(I16:R16)

Hope this Helps.
All the Best.
PAB

 

[GillesD]

Formula for

PAB

Very nice formulas in columns I to R to get the respective occurencess of numbers 0 to 9 as last digits from data in columns C to G. It sure works fine but I will have to look at it closely to fully understand it.

Finally, to get the answers Sammy was looking for, I would put in column S the following formula: =SUMIF(I5:R5; ">=2").

The only drawback (and really a small one) is the size of the file; it will be somewhat larger as this adds 11 formulas to each line of data. But this can be countered by changing formulas into data once calculations are made.

Thanks for this nice input PAB.

 

[Sammy]

Hi Pab,

Thank you for your for time and input - much appreciated.

Cheers,
Sammy

Hi Sammy,

Assuming that the First Five Numbers are in Cells C16:G16 for Example, you could Put the Following Formulas in Cells I16:R16 and Copy Down. These will give you the TOTAL for EACH of the LAST DIGITS for EACH ROW. You could then Perform whatever Analysis that you want.

In Cell I16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="0"))
In Cell J16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="1"))
In Cell K16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="2"))
In Cell L16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="3"))
In Cell M16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="4"))
In Cell N16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="5"))
In Cell O16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="6"))
In Cell P16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="7"))
In Cell Q16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="8"))
In Cell R16 Enter the Formula :-
=SUMPRODUCT(--(RIGHT($C16:$G16,1)="9"))

This Formula Should Produce a Total of Five for Each of the Rows.
In Cell S16 Enter the Formula :-
=SUM(I16:R16)

Hope this Helps.
All the Best.
PAB

 

[Sammy]

Re: Formula for

Hi GillesD,

Thanks for further input - SUMIF formula ties it together nicely.

Cheers,
Sammy

PAB

Very nice formulas in columns I to R to get the respective occurencess of numbers 0 to 9 as last digits from data in columns C to G. It sure works fine but I will have to look at it closely to fully understand it.

Finally, to get the answers Sammy was looking for, I would put in column S the following formula: =SUMIF(I5:R5; ">=2").

The only drawback (and really a small one) is the size of the file; it will be somewhat larger as this adds 11 formulas to each line of data. But this can be countered by changing formulas into data once calculations are made.

Thanks for this nice input PAB.

 

[PAB]

Hi GillesD,

Could you Explain why the Case Numbers are Not Sequential Along with the Criteria Being 2,4,5,4, & 5 for the Following Please :-

Select Case nDupl
Case 1
nDupl = 2
Case 2
nDupl = 4
Case 4
nDupl = 5
Case 6
nDupl = 4
Case 10
nDupl = 5
End Select

Thanks in Advance.
All the Best.
PAB

 

[PAB]

Hi Sammy,

I thought I would have a Play Around with this.
I have come up with the Following Macro that Provides the Information you Requested.
It Loops through the Columns Only Once, which should make it Slightly Quicker when Dealing with a Long List of Combinations.
Copy the Macro Below into a Standard Module.
Place the Cursor in the Cell ( Column "C" ) with the First Number of the First Combination and Run the Macro.
The Results will be Listed in Column "M".

Here is the Code :-

Option Explicit

Sub Duplicates()
Dim DigitCount(0 To 9) As Integer
Dim Duplicate As Integer
Dim i As Integer
Dim LastDigit As Integer

Application.ScreenUpdating = False

Do While ActiveCell <> ""

For i = 0 To 9
DigitCount(i) = 0
Next i

For i = 0 To 4
LastDigit = ActiveCell.Offset(0, i).Value - 10 * Int(ActiveCell.Offset(0, i).Value / 10)
DigitCount(LastDigit) = DigitCount(LastDigit) + 1
Next i

Duplicate = 0

For i = 0 To 9
If DigitCount(i) > 1 Then
Duplicate = Duplicate + DigitCount(i)
End If
Next i

ActiveCell.Offset(0, 10).Value = Duplicate
ActiveCell.Offset(1, 0).Select

Loop

Application.ScreenUpdating = True
End Sub

Good Luck!.
All the Best.
PAB

 

[Sammy]

Hi PAB,

Thank you very much - most appreciated.

Cheers,
Sammy

Hi Sammy,

I thought I would have a Play Around with this.
I have come up with the Following Macro that Provides the Information you Requested.
It Loops through the Columns Only Once, which should make it Slightly Quicker when Dealing with a Long List of Combinations.
Copy the Macro Below into a Standard Module.
Place the Cursor in the Cell ( Column "C" ) with the First Number of the First Combination and Run the Macro.
The Results will be Listed in Column "M".

Here is the Code :-

Option Explicit

Sub Duplicates()
Dim DigitCount(0 To 9) As Integer
Dim Duplicate As Integer
Dim i As Integer
Dim LastDigit As Integer

Application.ScreenUpdating = False

Do While ActiveCell <> ""

For i = 0 To 9
DigitCount(i) = 0
Next i

For i = 0 To 4
LastDigit = ActiveCell.Offset(0, i).Value - 10 * Int(ActiveCell.Offset(0, i).Value / 10)
DigitCount(LastDigit) = DigitCount(LastDigit) + 1
Next i

Duplicate = 0

For i = 0 To 9
If DigitCount(i) > 1 Then
Duplicate = Duplicate + DigitCount(i)
End If
Next i

ActiveCell.Offset(0, 10).Value = Duplicate
ActiveCell.Offset(1, 0).Select

Loop

Application.ScreenUpdating = True
End Sub

Good Luck!.
All the Best.
PAB

 

[PAB]

You're Welcome Sammy,

Out of Interest, what are you going to Use the Results Produced for. Have you Already got a Strategy in Mind?, and if so, what Analysis of the Results will you be Performing?.

All the Best.
PAB

 

[Sammy]

Hi PAB,

Just got back to Post. Until recently, I hadn't taken much notice of the analysis of LAST Digits in a Lotto Draw and thus completely ignored any tracking of them. However, thinking about it now seems ridiculous that I didn't make even a tenuous link previously; I mean our digit system is only 0-9 repeated with various combinations of the digits to make a 2 digit number or using one digit as a single number, and in our Lotto's the numbers are either single digit or double digit.

I'm using this information to look particularly at the UK 5/34 Thunderball Lotto that uses numbers 1-34, rather than the UK 6/49 Lotto which has more numbers in its main pool - it also has huge Jackpot prizes but I prefer to work with a smaller number pool. I feel I have a better chance to swing the Odds in my favour. That said, to win the UK 5/34 Thunderball Lotto you still have to get the bonus ball from 1/14 pool. So, the overall Odds of the UK 5/34 is much worse than the UK 6/49 Lotto but I still feel more comfortable working with the UK 5/34 Lotto. Alas a Jackpot win has eluded me thus far!

Just looking at the UK 5/34 Thunderball Lotto - in the 467 Draws to date - general analysis of LAST Digit:

1-1-1-1-1 167
2-1-1-1-0 232
2-2-1-0-0 51
3-1-1-0-0 17
3-2-0-0-0 0
4-1-0-0-0 0
5-0-0-0-0 0
Total 467

167 Draws where 0 Last Digits was the same - all unique
232 Draws where 2 Last Digits was the same - two matched
51 Draws where 2 sets Last Digits was the same - two sets of two matched
17 Draws where 3 Last Digits was the same

We can see above that 3 Last Digits the same has occurred only 17 times to date.

I haven't pulled it all together yet, but I think there is some mileage in this - perhaps, looking at the Expected and Actual Frequencies of matching or non-matching Last Digits etc, etc!

Further comments or insight welcome.

Cheers,
Sammy

You're Welcome Sammy,

Out of Interest, what are you going to Use the Results Produced for. Have you Already got a Strategy in Mind?, and if so, what Analysis of the Results will you be Performing?.

All the Best.
PAB

 

[johnph77]

Sammy -

Comments:

5/34 matrix has 278,256 possiblities; adding the Thunderball (1/14) brings the possibilities to 3,895,584 - still less than the 13,983,816 possibilities present in a 6/49 matrix.

If you're concentrating on the rightmost drawn digits, 1 through 4 will (naturally) appear more often than 5 through 0, as will pairs and triplets with these numbers.

gl

j

 

[Sammy]

Hi John,

Thank you very much for correcting my mathematical error with the main number pool 5/34 and bonus number pool 1/14 Odds. I feel quite positive now!

It's good to have someone spell things out no matter how obvious it may seem to yourself - your comments regarding the more frequent appearances of Righmost digits 1-4 along with pairs and triplets of the same digits have been duly noted. Better still, I'll incorporate the above in a number selection strategy.

Thanks again.
Sammy

Sammy -

Comments:

5/34 matrix has 278,256 possiblities; adding the Thunderball (1/14) brings the possibilities to 3,895,584 - still less than the 13,983,816 possibilities present in a 6/49 matrix.

If you're concentrating on the rightmost drawn digits, 1 through 4 will (naturally) appear more often than 5 through 0, as will pairs and triplets with these numbers.

gl

j

 

[johnph77]

Sammy -

A forum is a place for the exchange of information, generally on a particular subject. I'm glad I could help - and thank you for the kind words.

To further expound, and with no desire to appear overbearing or officious, or to beat a dead horse into the ground, I'll offer the following thoughts:

In the base 5/34 matrix, with the rightmost numbers being 1 through 4, quads are possible, i.e. 01-11-21-31-xx, xx being any other drawn number. If the rightmost number is 5 through 0, then only trips are possible. An increase in double and triple occurances of the rightmost numbers 1 through 4 must happen.

When the Thunderball (1/14) is included and the rightmost number is 1 through 4 you can wind up with quints - 01-11-21-31-41-xx plus either 01 or 11.

Note that if 1 through 4 is selected as a particular rightmost number it is twice as likely to appear as a Thunderball as will any particular selected number of 5 through 0. Given the Thunderball matrix, the numbers 1 through 4 will appear as the rightmost number 8 times out of 14, the numbers 5 through 0 will appear in that position 6 times out of 14.

gl

j

 

[PAB]

Hi Sammy,

I haven't pulled it all together yet, but I think there is some mileage in this - perhaps, looking at the Expected and Actual Frequencies of matching or non-matching Last Digits etc, etc!

Further comments or insight welcome.

Cheers,
Sammy

Seeing you are doing some Work on Last Digits for a 534 Lotto ( UK Thunderball ), I have Calculated the Following Table of the Total Combinations Available for EACH Distribution Possible. There are a Total of 278,256 ( Calculated Using the Excel Formula Combin(34,5) = 278,256 ) Combinations. If you want the Actual Excel Formulas Please let me Know and I will Post them.

Here is the Table :-
Distribution 11111 = 111,906 Combinations = 40.22%
Distribution 21110 = 135,420 Combinations = 48.67%
Distribution 22100 = 21,060 Combinations = 7.57%
Distribution 31100 = 8,940 Combinations = 3.21%
Distribution 32000 = 810 Combinations = 0.29%
Distribution 41000 = 120 Combinations = 0.04%
Total = 278,256 Combinations = 100.00%

What you could do in Another Column is Calculate the Last Digit Distribution for EACH Draw. If you would like to do this let me Know and I will Post the Formulas to Use.

Below is a Table of the EXPECTED, ACTUAL and DIFFERENCE to Date for EACH Distribution as at Draw 468 ( Saturday the 6th of August 2005 ).

Here is the Table :-
Distribution 11111, Expected = 188, Actual = 168, Difference = -20
Distribution 21110, Expected = 228, Actual = 233, Difference = +5
Distribution 22100, Expected = 35, Actual = 51, Difference = +16
Distribution 31100, Expected = 15, Actual = 16, Difference = +1
Distribution 32000, Expected = 1, Actual = 0, Difference = -1
Distribution 41000, Expected = 0, Actual = 0, Difference = 0
Total Expected = 468, Actual = 468, Difference = 0

NONE of the Above Information takes the Thunderball Number into Consideration. ALL the Above Information Deals ONLY with the 5 Main Numbers Drawn from 34.

Hope this Helps.
All the Best.
PAB

 

[PAB]

Hi Sammy,

Just some Extra Information you Might Find Useful.
The Table Below Lists the Total Combinations Available for EACH Distribution Possible, the Total Percentage Available and the Number of Draws that the Distribution is Expected to be Drawn Every.

Here is the Table :-
Distribution 11111 = 111,906 Combinations = 40.22% = 1 in 2.49 Draws
Distribution 21110 = 135,420 Combinations = 48.67% = 1 in 2.05 Draws
Distribution 22100 = 21,060 Combinations = 7.57% = 1 in 13.21 Draws
Distribution 31100 = 8,940 Combinations = 3.21% = 1 in 31.12 Draws
Distribution 32000 = 810 Combinations = 0.29% = 1 in 343.53 Draws
Distribution 41000 = 120 Combinations = 0.04% = 1 in 2,318.80 Draws

NONE of the Above Information takes the Thunderball Number into Consideration. ALL the Above Information Deals ONLY with the 5 Main Numbers Drawn from 34.

Hope this Helps.
All the Best.
PAB

 

[johnph77]

Sammy -

The following information is valid only only for a specific rightmost number selected in the base 5/34 matrix, with 278,256 possibilities.

If the specific rightmost number selected is 1 through 4 then there will be:

30 quads.
1,305 triples.
24,795 doubles.

If the specific rightmost number selected is 5 thorugh 0 then there will be:

0 quads.
465 triples.
13,485 doubles.

Note that the above figures do not take into consideration that there can be doubles or triples of one rightmost number and doubles of another in the same drawing. The program from which the above numbers have been derived have, however, eliminated the two doubles of the same number in the quads occurances.

I haven't done the programming necessary to include the Thunderball in this data yet and it may be a while before I get around to it.