Welcome Guest
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
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

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

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.

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.

=^.^=

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?

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.

=^.^=

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.

=^.^=

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.

 Page 1 of 1