Lotto program request
- Thread starter Kwc
- Start date
CMF
Member
Threes and Fours how to get them.
I recently did some work with threes and fours for all the world's 649 Lotto Games and combining four of the best fours with the best threes you end up with about a 37% return for the 3's, 4's and 5's instead of the 19% per Quick Picks or Probability calculations. This is a guide to the unattainable and a restraint on the spiel of our best Lotto System spruikers. What it means is in the long term you won't be ahead without a six.
Regarding getting these threes it's quite easy if you wean yourself off Excel and over to Access - things that you muck around with in Excel can be done almost automatically such as grouping and sorting in Access.
Regards
Colin
I recently did some work with threes and fours for all the world's 649 Lotto Games and combining four of the best fours with the best threes you end up with about a 37% return for the 3's, 4's and 5's instead of the 19% per Quick Picks or Probability calculations. This is a guide to the unattainable and a restraint on the spiel of our best Lotto System spruikers. What it means is in the long term you won't be ahead without a six.
Regarding getting these threes it's quite easy if you wean yourself off Excel and over to Access - things that you muck around with in Excel can be done almost automatically such as grouping and sorting in Access.
Regards
Colin
Lotto Program Request
Hi Everyone,
Here are the Excel Formulas to Enter into an Excel Worksheet to Calculate the Total Number of Matches ( 0, 1, 2, 3, 4, 5, 5+ & 6 ) for a Combination(s) in ALL Lotto Draws. These are for a 649 Lotto with One Bonus Number Drawn.
**************************************************
Lotto Draw Results Criteria.
In Cell B5 and Going Down, Enter the Draw Number.
In Cell C5 and Going Down, Enter the Draw Day if you Keep that Information, Otherwise Leave it Blank.
In Cell D5 and Going Down, Enter the Draw Date if you Keep that Information, Otherwise Leave it Blank.
In Cells E5:J5 and Going Down, Enter the Lotto Numbers Drawn.
In Cell K5 and Going Down, Enter the Bonus Ball Number.
The Cells Above B5:K5 can be Used for Titles Or Labels.
**************************************************
Combination(s) to Check Criteria.
In Cell M5 and Going Down, Enter Some Description so that you will know which Combination Number you are Checking.
In Cells N5:S5 and Going Down, Enter the Combinations to be Checked ( Leave Blank for the Time Being ).
The Cells Above M5:S5 can be Used for Titles Or Labels.
**************************************************
To Calculate the Number of Matches for EACH Combination for EACH Draw Criteria.
In Cells U4:IU4 Enter the Numbers 1 to 235, this will Make it Easier to see the Data Associated with the Combination Number to Check.
In Cell U1 ( Copy & Paste from Here ), Enter the Formula :-
="$N$" & COLUMN()-16 & ":$S$" & COLUMN()-16
This is so the Array of Numbers from Columns N to S DOESN'T Change as the Row Changes But DOES Change as the Column Changes when Copied Across in the Formula Below.
In Cell U5 ( Copy & Paste from Here ), Enter the Formula ( Array Entered, Ctrl + Shift + Enter will give you Curly Brackets at EACH End of the Formula, Do NOT Enter these yourself ) :-
=IF($N$5=0," ",IF(COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))=5,IF(COUNT(MATCH(INDIRECT(U$1),$K5,0))=1,"5+","5"),COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))))
Copy this Formula Down to Cell U1504, this will Give you 1,500 Worth of Draws To Date ( if Applicable ) of which to Check Against. If there are More than 1,500 Draws To Date, See the Note Below at the Bottom.
The Cells From U2:IU3 can be Used for Titles Or Labels.
**************************************************
Table of the Total Number of Times EACH Combination has Matched Criteria.
Set the Column Width for Column T to 17.
In Cell T1506, Enter the Text Matched 0
In Cell T1507, Enter the Text Matched 1
In Cell T1508, Enter the Text Matched 2
In Cell T1509, Enter the Text Matched 3
In Cell T1510, Enter the Text Matched 4
In Cell T1511, Enter the Text Matched 5
In Cell T1512, Enter the Text Matched 5+
In Cell T1513, Enter the Text Matched 6
In Cell T1514, Enter the Text Total Draws Tested
In Cell U1506 ( Copy & Paste from Here ), Enter the Formula ( for 0 Matched ) :-
=IF(SUM(U$1507:U$1513)=0,0,COUNTIF($E5:$E1506,">0")-SUM(U$1507:U$1513))
Yes, $E1506, then this Formula will Automatically Adjust if you Insert More Rows for More Lotto Draws ( See the Note Below at the Bottom ).
The Total Number of Times Matched for 0 is Calculated Using the Total Draws To Date LESS the Number of Matches Greater than 0.
In Cell U1507 ( Copy & Paste from Here ), Enter the Formula ( for 1 Matched ) :-
=COUNTIF(U$5:U$1504,"=1")
In Cell U1508 ( Copy & Paste from Here ), Enter the Formula ( for 2 Matched ) :-
=COUNTIF(U$5:U$1504,"=2")
In Cell U1509 ( Copy & Paste from Here ), Enter the Formula ( for 3 Matched ) :-
=COUNTIF(U$5:U$1504,"=3")
In Cell U1510 ( Copy & Paste from Here ), Enter the Formula ( for 4 Matched ) :-
=COUNTIF(U$5:U$1504,"=4")
In Cell U1511 ( Copy & Paste from Here ), Enter the Formula ( for 5 Matched ) :-
=COUNTIF(U$5:U$1504,"=5")
In Cell U1512 ( Copy & Paste from Here ), Enter the Formula ( for 5+ Matched ) :-
=COUNTIF(U$5:U$1504,"=5+")
In Cell U1513 ( Copy & Paste from Here ), Enter the Formula ( for 6 Matched ) :-
=COUNTIF(U$5:U$1504,"=6")
In Cell U1514 ( Copy & Paste from Here ), Enter the Formula ( for Total Draws Tested ) :-
=SUM(U1506:U1513)
**************************************************
What I would Personally do Now is Freeze the Panes at Row Number 5 ( Click Row Number 5, Click Window & Click Freeze Panes ), this will Make Viewing the Table of the Total Number of Times EACH Combination has Matched 0, 1, 2, 3, 4, 5, 5+ & 6 Far More Easier.
NOW, if you Insert 6 Numbers in Cells N5:S5, this will give you ALL the Information of Matching those 6 Numbers ( 1 Combination ) Against ALL the Lotto Draws To Date.
I would Personally Save the File NOW.
To get the Results for More than 1 Combination ( After you have Input More Combinations in Columns N to S ), Highlight Cells U1:U1514 ( Unless you have Inserted More Rows Because there are More than 1,500 Lotto Draws to Date, then the Last Cell Highlighted will be Greater than Cell U1514, See the Note Below ), and Using the Fill Handle, Drag it Across Until you Reach the Required Number of Combinations you are Checking.
This Single Worksheet will Allow you to Check 235 Combinations Against ALL the Draws To Date.
If there are More than 1,500 Lotto Draws, then Simply Click on Row Number 1504 and Drag Down for as Many Rows ( Draws ) that you want to Insert. ALL the Formulas for the Table of the Total Number of Times EACH Combination has Matched will Automatically be Adjusted.
One Important thing, Depending on How Many Combinations you want to Check, and the Total Number of Draws to Date, this will Create a VERY BIG Excel File. It Might be Best to Save the File Prior to ANY Copying Across ( where I Suggested Above ). That way, when to do Copy U1:U1514 Across, and Get the Required Information, you can then Close the File WITHOUT Saving it.
I have gone through the Information Above and Created the File, so Hopefully there will NOT be Any Problems.
To Make it Look Neater, you could Adjust Column Widths and Add Some Colour Maybe.
I Have Tried to Explain this with as Much Information and as Clearly as I can Because I know People have Different Levels of Worksheet Experience and Knowledge. If you Copy & Paste the Formulas where I have Indicated, I think that ALL will go According to Plan.
Have Fun Everyone.
All the Best.
PAB
Hi Everyone,
Here are the Excel Formulas to Enter into an Excel Worksheet to Calculate the Total Number of Matches ( 0, 1, 2, 3, 4, 5, 5+ & 6 ) for a Combination(s) in ALL Lotto Draws. These are for a 649 Lotto with One Bonus Number Drawn.
**************************************************
Lotto Draw Results Criteria.
In Cell B5 and Going Down, Enter the Draw Number.
In Cell C5 and Going Down, Enter the Draw Day if you Keep that Information, Otherwise Leave it Blank.
In Cell D5 and Going Down, Enter the Draw Date if you Keep that Information, Otherwise Leave it Blank.
In Cells E5:J5 and Going Down, Enter the Lotto Numbers Drawn.
In Cell K5 and Going Down, Enter the Bonus Ball Number.
The Cells Above B5:K5 can be Used for Titles Or Labels.
**************************************************
Combination(s) to Check Criteria.
In Cell M5 and Going Down, Enter Some Description so that you will know which Combination Number you are Checking.
In Cells N5:S5 and Going Down, Enter the Combinations to be Checked ( Leave Blank for the Time Being ).
The Cells Above M5:S5 can be Used for Titles Or Labels.
**************************************************
To Calculate the Number of Matches for EACH Combination for EACH Draw Criteria.
In Cells U4:IU4 Enter the Numbers 1 to 235, this will Make it Easier to see the Data Associated with the Combination Number to Check.
In Cell U1 ( Copy & Paste from Here ), Enter the Formula :-
="$N$" & COLUMN()-16 & ":$S$" & COLUMN()-16
This is so the Array of Numbers from Columns N to S DOESN'T Change as the Row Changes But DOES Change as the Column Changes when Copied Across in the Formula Below.
In Cell U5 ( Copy & Paste from Here ), Enter the Formula ( Array Entered, Ctrl + Shift + Enter will give you Curly Brackets at EACH End of the Formula, Do NOT Enter these yourself ) :-
=IF($N$5=0," ",IF(COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))=5,IF(COUNT(MATCH(INDIRECT(U$1),$K5,0))=1,"5+","5"),COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))))
Copy this Formula Down to Cell U1504, this will Give you 1,500 Worth of Draws To Date ( if Applicable ) of which to Check Against. If there are More than 1,500 Draws To Date, See the Note Below at the Bottom.
The Cells From U2:IU3 can be Used for Titles Or Labels.
**************************************************
Table of the Total Number of Times EACH Combination has Matched Criteria.
Set the Column Width for Column T to 17.
In Cell T1506, Enter the Text Matched 0
In Cell T1507, Enter the Text Matched 1
In Cell T1508, Enter the Text Matched 2
In Cell T1509, Enter the Text Matched 3
In Cell T1510, Enter the Text Matched 4
In Cell T1511, Enter the Text Matched 5
In Cell T1512, Enter the Text Matched 5+
In Cell T1513, Enter the Text Matched 6
In Cell T1514, Enter the Text Total Draws Tested
In Cell U1506 ( Copy & Paste from Here ), Enter the Formula ( for 0 Matched ) :-
=IF(SUM(U$1507:U$1513)=0,0,COUNTIF($E5:$E1506,">0")-SUM(U$1507:U$1513))
Yes, $E1506, then this Formula will Automatically Adjust if you Insert More Rows for More Lotto Draws ( See the Note Below at the Bottom ).
The Total Number of Times Matched for 0 is Calculated Using the Total Draws To Date LESS the Number of Matches Greater than 0.
In Cell U1507 ( Copy & Paste from Here ), Enter the Formula ( for 1 Matched ) :-
=COUNTIF(U$5:U$1504,"=1")
In Cell U1508 ( Copy & Paste from Here ), Enter the Formula ( for 2 Matched ) :-
=COUNTIF(U$5:U$1504,"=2")
In Cell U1509 ( Copy & Paste from Here ), Enter the Formula ( for 3 Matched ) :-
=COUNTIF(U$5:U$1504,"=3")
In Cell U1510 ( Copy & Paste from Here ), Enter the Formula ( for 4 Matched ) :-
=COUNTIF(U$5:U$1504,"=4")
In Cell U1511 ( Copy & Paste from Here ), Enter the Formula ( for 5 Matched ) :-
=COUNTIF(U$5:U$1504,"=5")
In Cell U1512 ( Copy & Paste from Here ), Enter the Formula ( for 5+ Matched ) :-
=COUNTIF(U$5:U$1504,"=5+")
In Cell U1513 ( Copy & Paste from Here ), Enter the Formula ( for 6 Matched ) :-
=COUNTIF(U$5:U$1504,"=6")
In Cell U1514 ( Copy & Paste from Here ), Enter the Formula ( for Total Draws Tested ) :-
=SUM(U1506:U1513)
**************************************************
What I would Personally do Now is Freeze the Panes at Row Number 5 ( Click Row Number 5, Click Window & Click Freeze Panes ), this will Make Viewing the Table of the Total Number of Times EACH Combination has Matched 0, 1, 2, 3, 4, 5, 5+ & 6 Far More Easier.
NOW, if you Insert 6 Numbers in Cells N5:S5, this will give you ALL the Information of Matching those 6 Numbers ( 1 Combination ) Against ALL the Lotto Draws To Date.
I would Personally Save the File NOW.
To get the Results for More than 1 Combination ( After you have Input More Combinations in Columns N to S ), Highlight Cells U1:U1514 ( Unless you have Inserted More Rows Because there are More than 1,500 Lotto Draws to Date, then the Last Cell Highlighted will be Greater than Cell U1514, See the Note Below ), and Using the Fill Handle, Drag it Across Until you Reach the Required Number of Combinations you are Checking.
This Single Worksheet will Allow you to Check 235 Combinations Against ALL the Draws To Date.
If there are More than 1,500 Lotto Draws, then Simply Click on Row Number 1504 and Drag Down for as Many Rows ( Draws ) that you want to Insert. ALL the Formulas for the Table of the Total Number of Times EACH Combination has Matched will Automatically be Adjusted.
One Important thing, Depending on How Many Combinations you want to Check, and the Total Number of Draws to Date, this will Create a VERY BIG Excel File. It Might be Best to Save the File Prior to ANY Copying Across ( where I Suggested Above ). That way, when to do Copy U1:U1514 Across, and Get the Required Information, you can then Close the File WITHOUT Saving it.
I have gone through the Information Above and Created the File, so Hopefully there will NOT be Any Problems.
To Make it Look Neater, you could Adjust Column Widths and Add Some Colour Maybe.
I Have Tried to Explain this with as Much Information and as Clearly as I can Because I know People have Different Levels of Worksheet Experience and Knowledge. If you Copy & Paste the Formulas where I have Indicated, I think that ALL will go According to Plan.
Have Fun Everyone.
All the Best.
PAB
bloubul
Member
Lotto Program Request
PAB
I've followed your instructions to the letter, but experience some problems with the Arry formula in column U5:U1504.
Its either blank or if I enter 6 numbers in N5:S5 it shows "FALSE" all the way, column T1506:T1513 is also not counting.
What could be the problem??????
Please help.
Thanx
BlouBul
PAB
I've followed your instructions to the letter, but experience some problems with the Arry formula in column U5:U1504.
Its either blank or if I enter 6 numbers in N5:S5 it shows "FALSE" all the way, column T1506:T1513 is also not counting.
What could be the problem??????
Please help.
Thanx
BlouBul
Hi bloubul,
As Far as the Array Formula in Cells U5:U1504.
In Cell U5 ( Copy & Paste from Here ), Enter the Formula :-
=IF($N$5=0," " ,IF(COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))=5,IF(COUNT(MATCH(INDIRECT(U$1),$K5,0))=1,"5+","5"),COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))))
Do NOT Press Enter, Press Ctrl + Shift + Enter Together, this will Convert the Formula to an Array Formula. The Formula Should then Look like this :-
{=IF($N$5=0," " ,IF(COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))=5,IF(COUNT(MATCH(INDIRECT(U$1),$K5,0))=1,"5+","5"),COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))))}
Notice the Curly Brackets at the Beginning and End of the Formula, if you have these then the Formula is Now an Array Formula and is OK. Copy this Formual Down to Cell U1504.
The Reason the Cells would be Blank is Because there are NO Numbers in the First Cell of the Combinations to Check ( Cells N5:N1504 ).
**************************************************
As Far as the Cells T1506:T1513 Not Counting.
They are NOT Supposed to Count, these Cells are JUST Titles for the Totals that are Produced in Columns U to Whatever the Number of Combinations you are Checking.
**************************************************
Let me know if Everything Goes OK.
Good Luck.
All the Best.
PAB
As Far as the Array Formula in Cells U5:U1504.
In Cell U5 ( Copy & Paste from Here ), Enter the Formula :-
=IF($N$5=0," " ,IF(COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))=5,IF(COUNT(MATCH(INDIRECT(U$1),$K5,0))=1,"5+","5"),COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))))
Do NOT Press Enter, Press Ctrl + Shift + Enter Together, this will Convert the Formula to an Array Formula. The Formula Should then Look like this :-
{=IF($N$5=0," " ,IF(COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))=5,IF(COUNT(MATCH(INDIRECT(U$1),$K5,0))=1,"5+","5"),COUNT(MATCH(INDIRECT(U$1),$E5:$J5,0))))}
Notice the Curly Brackets at the Beginning and End of the Formula, if you have these then the Formula is Now an Array Formula and is OK. Copy this Formual Down to Cell U1504.
The Reason the Cells would be Blank is Because there are NO Numbers in the First Cell of the Combinations to Check ( Cells N5:N1504 ).
**************************************************
As Far as the Cells T1506:T1513 Not Counting.
They are NOT Supposed to Count, these Cells are JUST Titles for the Totals that are Produced in Columns U to Whatever the Number of Combinations you are Checking.
**************************************************
Let me know if Everything Goes OK.
Good Luck.
All the Best.
PAB
bloubul
Member
Lotto Program Request
PAB
I'm still having problems friend.
Do you perhaps have a copy of GillesD Optimum Set spreadsheet.
If you do have it, may I put my complete draw for you on the Internet do download it and transfer my data to obtain the Optimum Set and to get your formula running.
Could you help me with it.
Thanks
BlouBul
PAB
I'm still having problems friend.
Do you perhaps have a copy of GillesD Optimum Set spreadsheet.
If you do have it, may I put my complete draw for you on the Internet do download it and transfer my data to obtain the Optimum Set and to get your formula running.
Could you help me with it.
Thanks
BlouBul
Hi bloubul,
What Problems are you Having.
Did you Copy and Paste ALL the Formulas into a Worksheet, Doing it this Way will Overcome Any Typo Errors, Especially Considering the Cell References ( Absolute Values etc ).
As Far as GillesD Optimum Set Spreadsheet, No I Don't have it.
That is a Totally Seperate Thread and Issue, and has Nothing to do with the Program Not Working. Any Questions Regarding this should be Kept in that Thread.
All the Best.
PAB
What Problems are you Having.
Did you Copy and Paste ALL the Formulas into a Worksheet, Doing it this Way will Overcome Any Typo Errors, Especially Considering the Cell References ( Absolute Values etc ).
As Far as GillesD Optimum Set Spreadsheet, No I Don't have it.
That is a Totally Seperate Thread and Issue, and has Nothing to do with the Program Not Working. Any Questions Regarding this should be Kept in that Thread.
All the Best.
PAB
Bloubul,
Did you Manage to Resolve your Problems.
If so, Perhaps you could Post what you did to get the Program Working Please, this will Benefit Others in this Group that Might Encounter the Same Problem.
GillesD,
Is this Exercise Achievable with a VB Macro.
My Program Above Creates a VERY Big File ( although I did Come Up with a Workaround ) that is VERY Slow and Unmanageable. A Macro would Probably do this in Seconds and would Only Produce Values for the Results, thereby doing away with the Memory Hungry Formulas.
The Macro would NOT Need to List the Actual Matches for EACH Combination for ALL Draws in a Column, it could Just Calculate the Total Number of Times Matched for 0, 1, 2, 3, 4, 5, 5 + the Bonus and 6, and then Put these 8 Totals Next to the Combination Being Checked. I think it would Probably be the Matching of 5 and the Bonus Ball that would cause the Biggest Problem.
Doing this with a Macro would Enable the Checking of More than the 235 Sets of Numbers ( in Columns N to S ) that my Program Allows for Due to the Column Limitation of Excel.
If you are Happy to come up with a Macro, I am Quite Happy to do some Testing Using my Program and your Macro on the Canadian and UK Lottos.
All the Best.
PAB
Did you Manage to Resolve your Problems.
If so, Perhaps you could Post what you did to get the Program Working Please, this will Benefit Others in this Group that Might Encounter the Same Problem.
GillesD,
Is this Exercise Achievable with a VB Macro.
My Program Above Creates a VERY Big File ( although I did Come Up with a Workaround ) that is VERY Slow and Unmanageable. A Macro would Probably do this in Seconds and would Only Produce Values for the Results, thereby doing away with the Memory Hungry Formulas.
The Macro would NOT Need to List the Actual Matches for EACH Combination for ALL Draws in a Column, it could Just Calculate the Total Number of Times Matched for 0, 1, 2, 3, 4, 5, 5 + the Bonus and 6, and then Put these 8 Totals Next to the Combination Being Checked. I think it would Probably be the Matching of 5 and the Bonus Ball that would cause the Biggest Problem.
Doing this with a Macro would Enable the Checking of More than the 235 Sets of Numbers ( in Columns N to S ) that my Program Allows for Due to the Column Limitation of Excel.
If you are Happy to come up with a Macro, I am Quite Happy to do some Testing Using my Program and your Macro on the Canadian and UK Lottos.
All the Best.
PAB
bloubul
Member
Lotto Program Request
PAB
Yes I manage to solve the problem 90%. I had to take the "ARRAY" formula out and replace it without the "ARRAY" in U5....U1504. (I'm using Office XP PRO, maybe there's a problem with my software?????)
The only outher problem is Columns "N5:S5" what numbers (combinations) must be put in there. If I put 1, 2, 7, or 1, 9, 11 it just give me "ZREO" all the way.
Can it be that 1, 2, 7 was never drawn together in a lotto from the 1st draw till to date?(440 draws)
BlouBul
PAB
Yes I manage to solve the problem 90%. I had to take the "ARRAY" formula out and replace it without the "ARRAY" in U5....U1504. (I'm using Office XP PRO, maybe there's a problem with my software?????)
The only outher problem is Columns "N5:S5" what numbers (combinations) must be put in there. If I put 1, 2, 7, or 1, 9, 11 it just give me "ZREO" all the way.
Can it be that 1, 2, 7 was never drawn together in a lotto from the 1st draw till to date?(440 draws)
BlouBul
Hi bloubul,
The Formula for Cells U5:U1504 ( and in Subsequent Cells to the Right ) MUST be an ARRAY Entered Formula Otherwise it will NOT Return the Correct Results. I Doubt Very Much that there is Anything Wrong with your Software. The Only thing I can think of, is that Maybe the Version you are Using ( in South Africa ), Requires for Example a Semi Colon Or Full Stop Instead of a Comma Or Something like that. I know that People in some Countries have to Build their Formulas Slightly Differently from the Way I do in the UK. Look at the Way you Build your Formulas and see if there are Any Differences from them to the Ones I Use. Perhaps Somebody with a Bit More Knowledge Regarding the Use of Excel in Different Countries can Advise you Further.
As Far as the Numbers Entered into Cells N5:S5 and Below, you can Enter 1, 2, 3, 4, 5 OR 6 Numbers and it will Still Return the Correct Results. This is of Course if you have Input the Formula in U5:U1504 ( and in Subsequent Cells to the Right ) as an ARRAY. The Best way to Check this would be to Input Figures that were Included in the First Draw of your Lotto, say 3 Numbers out of the Six, then go Down to T1506 and Look at the Results Just to the Right of the Titles. The Program can Check AND Give Results NO Matter whether you Input 1 Number OR 6 Numbers.
Has Anybody Else Tried the Program, and if so, have you Encountered ANY Problems.
Let me Know how you get on.
All the Best.
PAB
The Formula for Cells U5:U1504 ( and in Subsequent Cells to the Right ) MUST be an ARRAY Entered Formula Otherwise it will NOT Return the Correct Results. I Doubt Very Much that there is Anything Wrong with your Software. The Only thing I can think of, is that Maybe the Version you are Using ( in South Africa ), Requires for Example a Semi Colon Or Full Stop Instead of a Comma Or Something like that. I know that People in some Countries have to Build their Formulas Slightly Differently from the Way I do in the UK. Look at the Way you Build your Formulas and see if there are Any Differences from them to the Ones I Use. Perhaps Somebody with a Bit More Knowledge Regarding the Use of Excel in Different Countries can Advise you Further.
As Far as the Numbers Entered into Cells N5:S5 and Below, you can Enter 1, 2, 3, 4, 5 OR 6 Numbers and it will Still Return the Correct Results. This is of Course if you have Input the Formula in U5:U1504 ( and in Subsequent Cells to the Right ) as an ARRAY. The Best way to Check this would be to Input Figures that were Included in the First Draw of your Lotto, say 3 Numbers out of the Six, then go Down to T1506 and Look at the Results Just to the Right of the Titles. The Program can Check AND Give Results NO Matter whether you Input 1 Number OR 6 Numbers.
Has Anybody Else Tried the Program, and if so, have you Encountered ANY Problems.
Let me Know how you get on.
All the Best.
PAB
Hi bloubul,
Columns V:IU are Used to Calculate the Results for the Combinations Input in Columns N:S.
For Example, if you Wanted to Check 20 Combinations, you would Input them in Cells N5:S24. Then Highlight Cells U1:U1514 and Drag ( Using the Fill Handle ) Across to Column AN, Making 20 Columns with Formulas to Coincide with the Number of Combinations you are Checking.
If you Fill Across to Column IU the Formulas are Calculating Cells with NO Input. This will make the Worksheet VERY Slow to Work with.
Read my Summing up at the End of the Lotto Program Request Post, it Tells you how to get the Information you want and Still Keep it a Relatively Small File.
All the Best.
PAB
Columns V:IU are Used to Calculate the Results for the Combinations Input in Columns N:S.
For Example, if you Wanted to Check 20 Combinations, you would Input them in Cells N5:S24. Then Highlight Cells U1:U1514 and Drag ( Using the Fill Handle ) Across to Column AN, Making 20 Columns with Formulas to Coincide with the Number of Combinations you are Checking.
If you Fill Across to Column IU the Formulas are Calculating Cells with NO Input. This will make the Worksheet VERY Slow to Work with.
Read my Summing up at the End of the Lotto Program Request Post, it Tells you how to get the Information you want and Still Keep it a Relatively Small File.
All the Best.
PAB
bloubul
Member
PAB
The way I solved my problem.
Start on a new spreadsheet.
Begin building your database again.
Copy formula for “Column U1
Do not copy formula to column U5:U1504 yet.
Enter any numbers in columns N5:S5 (not the same as the first draw)
Complete columns V4:IU4 (1 – 234)
Now only copy formula to column U5, but not as an “ARRAY”, it will display “0”.
Once you’ve got “0”, highlight Cell U5, in the formula Bar you will see the formula, click inside as if you want to edit, and press Ctrl+Shift+Enter to change to “ARRAY”
Now copy “ARRAY” formula down to U1504.
Now you can enter numbers in column N5:S5 and you will see column U5:U1504 will start counting as well as column T1506:T1514
All columns will add up now.
I hope this will help
Thanks again
BlouBul
The way I solved my problem.
Start on a new spreadsheet.
Begin building your database again.
Copy formula for “Column U1
Do not copy formula to column U5:U1504 yet.
Enter any numbers in columns N5:S5 (not the same as the first draw)
Complete columns V4:IU4 (1 – 234)
Now only copy formula to column U5, but not as an “ARRAY”, it will display “0”.
Once you’ve got “0”, highlight Cell U5, in the formula Bar you will see the formula, click inside as if you want to edit, and press Ctrl+Shift+Enter to change to “ARRAY”
Now copy “ARRAY” formula down to U1504.
Now you can enter numbers in column N5:S5 and you will see column U5:U1504 will start counting as well as column T1506:T1514
All columns will add up now.
I hope this will help
Thanks again
BlouBul
Hi bloubul,
I am Glad that you Now have a Working Database.
You Cannot Copy the Formula from here to Cell U5 as an ARRAY Formula, an ARRAY Formula does NOT Work like that. You can However, Copy the Formula from here and Instead of Pressing Enter, Press Ctrl + Shift + Enter which will give you Curly Brackets at EACH End of the Formula.
BTW, Column IU will be the 235th Combination Checked, NOT the 234th.
Just to Check that My Instructions were Correct and Understandable, I Re-Created the Database Using them and had NO Problems at ALL. The Main thing Now is that you can Check Any Criteria that you Set.
Have a Good Weekend.
All the Best.
PAB
I am Glad that you Now have a Working Database.
You Cannot Copy the Formula from here to Cell U5 as an ARRAY Formula, an ARRAY Formula does NOT Work like that. You can However, Copy the Formula from here and Instead of Pressing Enter, Press Ctrl + Shift + Enter which will give you Curly Brackets at EACH End of the Formula.
BTW, Column IU will be the 235th Combination Checked, NOT the 234th.
Just to Check that My Instructions were Correct and Understandable, I Re-Created the Database Using them and had NO Problems at ALL. The Main thing Now is that you can Check Any Criteria that you Set.
Have a Good Weekend.
All the Best.
PAB
Hi GillesD,
Is this Exercise Achievable with a VB Macro.
My Program Above Creates a VERY Big File ( although I did Come Up with a Workaround ) that is VERY Slow and Unmanageable. A Macro would Probably do this in Seconds and would Only Produce Values for the Results, thereby doing away with the Memory Hungry Formulas.
The Macro would NOT Need to List the Actual Matches for EACH Combination for ALL Draws in a Column, it could Just Calculate the Total Number of Times Matched for 0, 1, 2, 3, 4, 5, 5 + the Bonus and 6, and then Put these 8 Totals Next to the Combination Being Checked. I think it would Probably be the Matching of 5 and the Bonus Ball that would cause the Biggest Problem.
Doing this with a Macro would Enable the Checking of More than the 235 Sets of Numbers ( in Columns N to S ) that my Program Allows for Due to the Column Limitation of Excel.
If you are Happy to come up with a Macro, I am Quite Happy to do some Testing Using my Program and your Macro on the Canadian and UK Lottos.
I have Tried Creating a Macro But to NO Avail.
For Example, if we had in Cells :-
E5:J1000 = the 6 Main Lotto Numbers Drawn.
K5:K1000 = the Bonus Numbers Drawn.
N5:S1000 = the Combinations to Check for ALL Draws.
U5:AB1000 = the Total Times Matched for Each Category ( 0, 1, 2, 3, 4, 5, 5 + the Bonus and 6 ).
AC5:AC1000 = the Total Draws Tested.
The Biggest Downfall Using Excel as you know is the Memory Hungry Formulas and the Calculation Time Needed.
Thanks in Advance.
All the Best.
PAB
Is this Exercise Achievable with a VB Macro.
My Program Above Creates a VERY Big File ( although I did Come Up with a Workaround ) that is VERY Slow and Unmanageable. A Macro would Probably do this in Seconds and would Only Produce Values for the Results, thereby doing away with the Memory Hungry Formulas.
The Macro would NOT Need to List the Actual Matches for EACH Combination for ALL Draws in a Column, it could Just Calculate the Total Number of Times Matched for 0, 1, 2, 3, 4, 5, 5 + the Bonus and 6, and then Put these 8 Totals Next to the Combination Being Checked. I think it would Probably be the Matching of 5 and the Bonus Ball that would cause the Biggest Problem.
Doing this with a Macro would Enable the Checking of More than the 235 Sets of Numbers ( in Columns N to S ) that my Program Allows for Due to the Column Limitation of Excel.
If you are Happy to come up with a Macro, I am Quite Happy to do some Testing Using my Program and your Macro on the Canadian and UK Lottos.
I have Tried Creating a Macro But to NO Avail.
For Example, if we had in Cells :-
E5:J1000 = the 6 Main Lotto Numbers Drawn.
K5:K1000 = the Bonus Numbers Drawn.
N5:S1000 = the Combinations to Check for ALL Draws.
U5:AB1000 = the Total Times Matched for Each Category ( 0, 1, 2, 3, 4, 5, 5 + the Bonus and 6 ).
AC5:AC1000 = the Total Draws Tested.
The Biggest Downfall Using Excel as you know is the Memory Hungry Formulas and the Calculation Time Needed.
Thanks in Advance.
All the Best.
PAB