Welcome Guest
You last visited December 12, 2017, 10:15 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel formula

Topic closed. 12 replies. Last post 8 years ago by winsumloosesum.

 Page 1 of 1

Need help with formulas

 Detailed information [ 1 ] [20.00%] Step by step [ 4 ] [80.00%] Total Valid Votes [ 5 ] Discarded Votes [ 1 ]
Los Angeles
United States
Member #75410
June 2, 2009
520 Posts
Offline
 Posted: April 1, 2010, 12:39 am - IP Logged

Hi everyone,

I need help ; Is there a formula that would create all the combinations for the mega millions game ( 5/56 ) by draging it all the way to 3,819,816 in a numeric order ? I'm working with Excel 2007 Thank you.

My second question : Is there a formula to compt how many combinaitions it would be by position of the number ? for exemple for the ( 5/56 ) game if the number 45 is the 4th place in the combinaition of 5 how many combination would that be ?

I realy apreciate any help Hope you anderstand my questions ? Thank you all.

Frenchie.

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 1, 2010, 2:28 am - IP Logged

Hi everyone,

I need help ; Is there a formula that would create all the combinations for the mega millions game ( 5/56 ) by draging it all the way to 3,819,816 in a numeric order ? I'm working with Excel 2007 Thank you.

My second question : Is there a formula to compt how many combinaitions it would be by position of the number ? for exemple for the ( 5/56 ) game if the number 45 is the 4th place in the combinaition of 5 how many combination would that be ?

I realy apreciate any help Hope you anderstand my questions ? Thank you all.

Hey Frenchie,

I'm using Excel 2002 and have a limit of 65,000 or so cells per columns so over 3,819,816 is a bit much.

Here is an Excel file with all the totals based on your request.

http://www.box.net/shared/vq8xgqnfbq

The answer to your question is there are a total of 145684 combinations if the number "45" is in the 4th position.  Of course this does not include bonus balll calculations.  The calculations in the Excel file are based on a 5/56 game with no bonus ball.

Hope this is what you were looking for!!

Good luck!!

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 1, 2010, 3:06 am - IP Logged

Here is the download link to an Excel file that contains a Macro that will generate all 5/56 into 57 to 58 columns each column holding about 65,000 combinations. Does NOT include Bonus Balls)

http://www.box.net/shared/b27fms0gll

2.  Open the file

3.  Go to the main Menu and select Tools > Macro > Macros and then highlight the Macro then click Run.  Depending on the speed of your computer it may take about 10 minutes to complete.  May help to close all open programs except this Excel file.

4.  Since you are using Excel 2007 and this Macro was intended for Excel 2002 you may need to convert the VBA code.  Excel 2007 may do this automatically or if not you may need to do a Google search to see how to convert VBA Macros from Excel 2002 to Excel 2007.

Los Angeles
United States
Member #75410
June 2, 2009
520 Posts
Offline
 Posted: April 1, 2010, 10:30 pm - IP Logged

Thank you for your prompt response to my question but what Im looking for is to have all the combinations, all 3million plus, individually on a Excel sheet with the formula that creates them.     Thank You very much for all your help

New Member

United States
Member #73065
April 4, 2009
14 Posts
Offline
 Posted: April 2, 2010, 10:48 am - IP Logged

Here is the download link to an Excel file that contains a Macro that will generate all 5/56 into 57 to 58 columns each column holding about 65,000 combinations. Does NOT include Bonus Balls)

http://www.box.net/shared/b27fms0gll

2.  Open the file

3.  Go to the main Menu and select Tools > Macro > Macros and then highlight the Macro then click Run.  Depending on the speed of your computer it may take about 10 minutes to complete.  May help to close all open programs except this Excel file.

4.  Since you are using Excel 2007 and this Macro was intended for Excel 2002 you may need to convert the VBA code.  Excel 2007 may do this automatically or if not you may need to do a Google search to see how to convert VBA Macros from Excel 2002 to Excel 2007.

I get the message that this link has been removed. Is there a new link?

Thanks!

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 2, 2010, 10:48 pm - IP Logged

I get the message that this link has been removed. Is there a new link?

Thanks!

http://www.box.net/shared/yh4f1vdzqk

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 2, 2010, 11:01 pm - IP Logged

Hi everyone,

I need help ; Is there a formula that would create all the combinations for the mega millions game ( 5/56 ) by draging it all the way to 3,819,816 in a numeric order ? I'm working with Excel 2007 Thank you.

My second question : Is there a formula to compt how many combinaitions it would be by position of the number ? for exemple for the ( 5/56 ) game if the number 45 is the 4th place in the combinaition of 5 how many combination would that be ?

I realy apreciate any help Hope you anderstand my questions ? Thank you all.

This is the response to your 2nd question:

Below I circled how many combinations number 45 are in Postion 4 = 145,684.  In addition all 56 numbers combinations by positions 1 through 5.  This does not include Bonus Ball which of course would make the number of combinations larger.

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 3, 2010, 9:56 am - IP Logged

Thank you for your prompt response to my question but what Im looking for is to have all the combinations, all 3million plus, individually on a Excel sheet with the formula that creates them.     Thank You very much for all your help

If someone that has Excel 2007 would run the Macro to see if the combinations are generated would be appreciated.  This may take about 10 minutes to complete for 3 million plus combinations of a 5/56 game.

Excel 2007 grid is 1,048,576 rows by 16,384 columns so you could not list all 3 million plus combinations in 1 row.  It would be 3 plus rows vs the 58 rows for Excel 2002 version with 65,000 combinations per row.  Not if it makes a difference.

If someone who has Excel 2007 would run the Macro to see if it works and posts the results would be appreciated.  See instructions in previous thread to run macro.

Thanks!!

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6161 Posts
Offline
 Posted: April 3, 2010, 10:54 am - IP Logged

Steve, the macro works. In 07, you will have 59 columns X 65,000 rows.

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 3, 2010, 12:59 pm - IP Logged

Steve, the macro works. In 07, you will have 59 columns X 65,000 rows.

Thanks Carbob

For Excel 2007 owners you can change the following code:

' Verification of number of combinations posted
' and move to top of next column if 65,000

If N = 65001 Then
Selection.ColumnWidth = 15
N = 1
ActiveCell.Offset(-65000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Value = "Combinations"
End If

Change to:

' Verification of number of combinations posted
' and move to top of next column if 1,000,000

If N = 1000000 Then
Selection.ColumnWidth = 15
N = 1
ActiveCell.Offset(-1000000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Value = "Combinations"
End If

========================================

Special thanks to GillesD at Lotto649 for the Excel VBA Code

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 3, 2010, 1:01 pm - IP Logged

Thanks Carbob

For Excel 2007 owners you can change the following code:

' Verification of number of combinations posted
' and move to top of next column if 65,000

If N = 65001 Then
Selection.ColumnWidth = 15
N = 1
ActiveCell.Offset(-65000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Value = "Combinations"
End If

Change to:

' Verification of number of combinations posted
' and move to top of next column if 1,000,000

If N = 1000000 Then
Selection.ColumnWidth = 15
N = 1
ActiveCell.Offset(-1000000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Value = "Combinations"
End If

========================================

Special thanks to GillesD at Lotto649 for the Excel VBA Code

Carbob can you test this also by changing the VBA Code and re-run?

Let me know if it worked.

Thanks!!

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6161 Posts
Offline
 Posted: April 3, 2010, 3:10 pm - IP Logged

Carbob can you test this also by changing the VBA Code and re-run?

Let me know if it worked.

Thanks!!

Steve, it works, but it works faster the original way. A million rows is a lot of rows!!!! lol

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: April 3, 2010, 8:44 pm - IP Logged

Thanks Bob,

I kind of figured that.

 Page 1 of 1