Welcome Guest
Log In | Register )
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
PrintE-mailLink

Need help with formulas

Detailed information [ 1 ]  [20.00%]
Step by step [ 4 ]  [80.00%]
Total Valid Votes [ 5 ]  
Discarded Votes [ 1 ]  
frenchie's avatar - Lottery-041.jpg
Thread Starter
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.

    winsumloosesum's avatar - Lottery-060.jpg
    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!!

      winsumloosesum's avatar - Lottery-060.jpg
      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

      1.  Download the Excel file

      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.

        frenchie's avatar - Lottery-041.jpg
        Thread Starter
        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

          Avatar
          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

          1.  Download the Excel file

          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!

            winsumloosesum's avatar - Lottery-060.jpg
            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!

            Here is the new download link:

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

              winsumloosesum's avatar - Lottery-060.jpg
              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.

                winsumloosesum's avatar - Lottery-060.jpg
                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!!

                  CARBOB's avatar - ga lottery.png.gif
                  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.

                    winsumloosesum's avatar - Lottery-060.jpg
                    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

                      winsumloosesum's avatar - Lottery-060.jpg
                      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!!

                        CARBOB's avatar - ga lottery.png.gif
                        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

                          winsumloosesum's avatar - Lottery-060.jpg
                          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.