Welcome Guest
Log In | Register )
You last visited January 17, 2017, 7:50 am
All times shown are
Eastern Time (GMT-5:00)

Stuck on Excel formula

Topic closed. 8 replies. Last post 10 years ago by Just6ntlc.

Page 1 of 1
PrintE-mailLink
Avatar
New Member

United States
Member #49886
February 18, 2007
4 Posts
Offline
Posted: February 18, 2007, 2:16 pm - IP Logged

Hi ya'll,
I'm having a bit of a problem with trying to make up a formula for my project, hoping someone can help.....what I need is this..

Take a number say 198, in cell A1 for example..
I need a formula which will list all the possible 6 number group combinations utilizing the number range 1 thru 56 for the first 5 groups and the number range 1 thru 46 for the 6th number group which when added up will total
198, examples:

22 33 35 40 53 15 = 198
or
5 9 15 21 32 9 = 91
Numbers in the first 5 number groups can't repeat, they all use the same number range 1-56. The last number group has it's own number range of
1-46, so it's possible for numbers to repeat here. Know there are many combinations/possibilities but if someone could come up with a formula for this I'd appreciate it, tks.

Griz

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Offline
    Posted: February 19, 2007, 9:09 pm - IP Logged

    Griz

    I understand you want to enter a number in cell a1, generate a unique 5 # comb with numbers between 1 and 56, plus 1 # between 1 and 46, then compare the total to value in a1, if it matches keep it else do next comb.

     I think you will need a VB program within excel to do this. some one here can probably tell you how many hundred thousand combinations match your criteria.  with such a huge volume of possible comb why not use the rand function to generate comb then select from that list.

    I did something similiar years ago in Gwbasic on a lottery that only had 12 million combinations.  a week later I had the results, another forest of numbers and a choked off hard drive.  no closer to a good number.

     sorry I can't help

    p8 

      Avatar
      Greenwich, CT
      United States
      Member #4793
      May 24, 2004
      1822 Posts
      Offline
      Posted: February 20, 2007, 12:32 pm - IP Logged

      This is something that I could use help with also, the ability to create filters.

        johnph77's avatar - avatar
        CA
        United States
        Member #2987
        December 10, 2003
        832 Posts
        Offline
        Posted: February 21, 2007, 10:32 am - IP Logged

        I ran a quick program on the 5/56+1/46 matrix with a sum of 198 - if I'm correct there are 1,309,852 combinations that meet that criteria. Without going into the rest of the programming I would think the most popular sum would be around 163 or thereabouts, and there would be 1,833,414 combinations with that sum.

        gl

        j

        Blessed Saint Leibowitz, keep 'em dreamin' down there..... 

        Next week's convention for Psychics and Prognosticators has been cancelled due to unforeseen circumstances.

         =^.^=

          Avatar
          Greenwich, CT
          United States
          Member #4793
          May 24, 2004
          1822 Posts
          Offline
          Posted: February 22, 2007, 9:06 am - IP Logged

          John, thanks for the info.  Do you have any advice on how to create these filtering programs?

            johnph77's avatar - avatar
            CA
            United States
            Member #2987
            December 10, 2003
            832 Posts
            Offline
            Posted: February 22, 2007, 9:29 am - IP Logged

            JAG331 -

            The program I set up was for a specific sum rather than a range. The programming could be redone, and it's simple coding, just like I had originally done to provide a check on my sum charts. It never occurred to me to set up a sum chart to include the bonus ball for the more popular two-field games such as PowerBall and MegaMillions, but it can be done.....

            gl

            j

            Blessed Saint Leibowitz, keep 'em dreamin' down there..... 

            Next week's convention for Psychics and Prognosticators has been cancelled due to unforeseen circumstances.

             =^.^=

              johnph77's avatar - avatar
              CA
              United States
              Member #2987
              December 10, 2003
              832 Posts
              Offline
              Posted: February 22, 2007, 11:52 pm - IP Logged

              I ran a quick program on the 5/56+1/46 matrix with a sum of 198 - if I'm correct there are 1,309,852 combinations that meet that criteria. Without going into the rest of the programming I would think the most popular sum would be around 163 or thereabouts, and there would be 1,833,414 combinations with that sum.

              gl

              j

              Correction:

              Sum 198 - 1,309,852 occurences.

              Sum 166 - 1,833,414 occurences (most frequent).

              Sum 163 - 1,828,041 occurences.

              Program results not yet confirmed with spreadsheet analysis.

              gl

              j

              Blessed Saint Leibowitz, keep 'em dreamin' down there..... 

              Next week's convention for Psychics and Prognosticators has been cancelled due to unforeseen circumstances.

               =^.^=

                johnph77's avatar - avatar
                CA
                United States
                Member #2987
                December 10, 2003
                832 Posts
                Offline
                Posted: February 26, 2007, 12:50 am - IP Logged

                Sum chart for 5/56+1/46 lottery matrix:

                ``````````````````````````````````````````````````````016```````017```````018```````019```````020
                ```````````````````````````````````````````````````````````1`````````2`````````4`````````7````````12
                ----------------------------------------------------------------------------------------------------
                ````021```````022```````023```````024```````025```````026```````027```````028```````029```````030
                ````````19````````29````````42````````60````````83```````113```````150```````197```````254```````324
                ----------------------------------------------------------------------------------------------------
                ````031```````032```````033```````034```````035```````036```````037```````038```````039```````040
                ```````408```````509```````628```````769```````933``````1125``````1346``````1601``````1892``````2225
                ----------------------------------------------------------------------------------------------------
                ````041```````042```````043```````044```````045```````046```````047```````048```````049```````050
                ``````2602``````3029``````3509``````4049``````4652``````5326``````6074``````6905``````7823``````8837
                ----------------------------------------------------------------------------------------------------
                ````051```````052```````053```````054```````055```````056```````057```````058```````059```````060
                ``````9952`````11178`````12520`````13989`````15591`````17338`````19236`````21298`````23531`````25949
                ----------------------------------------------------------------------------------------------------
                ````061```````062```````063```````064```````065```````066```````067```````068```````069```````070
                `````28560`````31377`````34410`````37674`````41178`````44938`````48964`````53272`````57873`````62783
                ----------------------------------------------------------------------------------------------------
                ````071```````072```````073```````074```````075```````076```````077```````078```````079```````080
                `````68013`````73579`````79493`````85770`````92422`````99465````106909````114770````123059````131790
                ----------------------------------------------------------------------------------------------------
                ````081```````082```````083```````084```````085```````086```````087```````088```````089```````090
                ````140794````150624````160750````171365````182478````194100````206239````218906````232107````245851
                ----------------------------------------------------------------------------------------------------
                ````091```````092```````093```````094```````095```````096```````097```````098```````099```````100
                ````620144````274992````290400````306373````322913````340024````357706````375960````394785````414180
                ----------------------------------------------------------------------------------------------------
                ````101```````102```````103```````104```````105```````106```````107```````108```````109```````110
                ````434141````454664````475744````497374````519546````542251````565477````589213````613445````638157
                ----------------------------------------------------------------------------------------------------
                ````111```````112```````113```````114```````115```````116```````117```````118```````119```````120
                ````663333````688954````715000````741450````768281````795468````822985````850805````878898````907234
                ----------------------------------------------------------------------------------------------------
                ````121```````122```````123```````124```````125```````126```````127```````128```````129```````130
                ````935782````964507````993377```1022356```1051408```1080496```1109583```1138629```1167597```1196446
                ----------------------------------------------------------------------------------------------------
                ````131```````132```````133```````134```````135```````136```````137```````138```````139```````140
                ```1225137```1253628```1281881```1309852```1337503```1364791```1391676```1418116```1444073```1469503
                ----------------------------------------------------------------------------------------------------
                ````141```````142```````143```````144```````145```````146```````147```````148```````149```````150
                ```1494370```1518631```1542250```1565186```1587405```1608866```1629537```1649380```1668364```1686453
                ----------------------------------------------------------------------------------------------------
                ````151```````152```````153```````154```````155```````156```````157```````158```````159```````160
                ```1703620```1719829```1735057```1749272```1762452```1774569```1755605```1795534```1804343```1812010
                ----------------------------------------------------------------------------------------------------
                ````161```````162```````163```````164```````165```````166```````167```````168```````169```````170
                ```1818525```1823870```1828041```1831023```1832817```1833414```1832817```1831023```1828041```1823870
                ----------------------------------------------------------------------------------------------------
                ````171```````172```````173```````174```````175```````176```````177```````178```````179```````180
                ```1818525```1812010```1804343```1795534```1785605```1774569```1762452```1749272```1735057```1719829
                ----------------------------------------------------------------------------------------------------
                ````181```````182```````183```````184```````185```````186```````187```````188```````189```````190
                ```1703620```1686453```1668364```1649380```1629537```1608866```1587405```1565186```1542250```1518631
                ----------------------------------------------------------------------------------------------------
                ````191```````192```````193```````194```````195```````196```````197```````198```````199```````200
                ```1494370```1469503```1444073```1418116```1391676```1364791```1337503```1309852```1281881```1253628
                ----------------------------------------------------------------------------------------------------
                ````201```````202```````203```````204```````205```````206```````207```````208```````209```````210
                ```1225137```1196446```1167597```1138629```1109583```1080496```1051408```1022356````993377````964507
                ----------------------------------------------------------------------------------------------------
                ````211```````212```````213```````214```````215```````216```````217```````218```````219```````220
                ````935782````907234````878898````850805````822985````795468````768281````741450````715000````688954
                ----------------------------------------------------------------------------------------------------
                ````221```````222```````223```````224```````225```````226```````227```````228```````229```````230
                ````663333````638157````613445````589213````565477````542251````519546````497374````475744````454664
                ----------------------------------------------------------------------------------------------------
                ````231```````232```````233```````234```````235```````236```````237```````238```````239```````240
                ````434141````414180````394785````375960````357706````340024````322913````306373````290400````274992
                ----------------------------------------------------------------------------------------------------
                ````241```````242```````243```````244```````245```````246```````247```````248```````249```````250
                ````260144````245851````232107````210906````206239````194100````182478````171365````160750````150624
                ----------------------------------------------------------------------------------------------------
                ````251```````252```````253```````254```````255```````256```````257```````258```````259```````260
                ````140974````131790````123059````114770````106909`````99465`````92422`````85770`````79493`````73579
                ----------------------------------------------------------------------------------------------------
                ````261```````262```````263```````264```````265```````266```````267```````268```````269```````270
                `````68013`````62783`````57873`````53272`````48964`````44938`````41178`````37674`````34410`````31377
                ----------------------------------------------------------------------------------------------------
                ````271```````272```````273```````274```````275```````276```````277```````278```````279```````280
                `````28560`````25949`````23531`````21298`````19236`````17338`````15591`````13989`````12520`````11178
                ----------------------------------------------------------------------------------------------------
                ````281```````282```````283```````284```````285```````286```````287```````288```````289```````290
                ``````9952``````8837``````7823``````6905``````6074``````5326``````4652``````4049``````3509``````3029
                ----------------------------------------------------------------------------------------------------
                ````291```````292```````293```````294```````295```````296```````297```````298```````299```````300
                ``````2602``````2225``````1892``````1601``````1346``````1125```````933```````769```````628```````509
                ----------------------------------------------------------------------------------------------------
                ````301```````302```````303```````304```````305```````306```````307```````308```````309```````310
                ```````408```````324```````254```````197```````150```````113````````83````````60````````42````````29
                ----------------------------------------------------------------------------------------------------
                ````311```````312```````313```````314```````315```````316
                ````````19````````12`````````7`````````4`````````2`````````1

                Program results not yet confirmed by spreadsheet analysis.

                gl

                j

                Blessed Saint Leibowitz, keep 'em dreamin' down there..... 

                Next week's convention for Psychics and Prognosticators has been cancelled due to unforeseen circumstances.

                 =^.^=


                  United States
                  Member #16612
                  June 2, 2005
                  3493 Posts
                  Offline
                  Posted: February 28, 2007, 11:42 pm - IP Logged

                  That's a great possibilities on number occurrences.