Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Quad combinations
" wrote...
Can anyone tell me how to adapt the follwing formula so kindly provided by Mr. Grove, to enable Excel to generate all the possible combin(49,4): ... You really want all 211876 combinations of 4 numbers from 49 numbers without replacement (i.e., no duplicates)? Results in multiple 4-column ranges all the way from row 1 down to row 65536? Far more efficient to use 4 columns. A1: 1 B1: 2 C1: 3 D1: 4 Then enter the following formulas. A2: =A1+(B1=47) B2: =IF(C1<48,B1,IF(B1<47,B1,A2)+1) C2: =IF(D1<49,C1,IF(C1<48,C1,B2)+1) D2: =IF(D1<49,D1,C2)+1 F1: =A65536+(B65536=47) G1: =IF(C65536<48,B65536,IF(B65536<47,B65536,F1)+1) H1: =IF(D65536<49,C65536,IF(C65536<48,C65536,G1)+1) I1: =IF(D65536<49,D65536,H1)+1 Select F1:I1, copy, and paste into K1:N1 *and* P1:S1. Don't worry about the values they show at this point. Select A2:D2, and fill down into A3:D65536. Then select A2:D65536, copy, and paste into F2:I65536 and K2:N65536. Select A2:D2, copy, and paste into P2:S15268. How you choose to work with this is up to you. Inaddition can someone provide the most efficient formula that would enable me to determine the frequency of repetitive quad combinations in a sample of lottery results. I have a lottery table with all lottery results in (Columns B:H). The lottery has 49 balls and 6 balls and the bonus ball are drawn. B C etc.... 1st No 2nd No etc.... Meaning any combination of 4 of the 7 balls in column B through H? That's 35 possible combinations from each row. So, you'd be better off hardcoding all combinations of (7 choose 4) in another range, namely, 1 2 3 4 1 2 3 5 1 2 3 6 1 2 3 7 1 2 4 5 1 2 4 6 1 2 4 7 1 2 5 6 1 2 5 7 1 2 6 7 1 3 4 5 1 3 4 6 1 3 4 7 1 3 5 6 1 3 5 7 1 3 6 7 1 4 5 6 1 4 5 7 1 4 6 7 1 5 6 7 2 3 4 5 2 3 4 6 2 3 4 7 2 3 5 6 2 3 5 7 2 3 6 7 2 4 5 6 2 4 5 7 2 4 6 7 2 5 6 7 3 4 5 6 3 4 5 7 3 4 6 7 3 5 6 7 4 5 6 7 and call that Comb_7_4. If your range of samples (B2:H99, for example) were named Samples, then in yet another range, the top-left cell of which I'll name Quads, generate the combinations of 4 out of 7 using these formulas. the Quads cell itself: =INDEX(Samples,1+INT((ROW()-ROW(Quads))/35), INDEX(Comb_7_4,1+MOD(ROW()-ROW(Quads),35),COLUMN()-COLUMN(Quads)+1)) Fill this right into the next 3 columns. Select this 4-column by 1-row range, and fill down into the next 3429 rows (this is specific to the number of rows needed for Samples being B2:H99, i.e., 35 * 98 = 3430). In the column to the right of this range of formulas enter a formula like (this one assumes Quads is W2) 4 columns to the right of Quads (AA2 in this example): =W2&" "&X2&" "&Y2&" "&Z2 and fill this down into the same row as the range of formulas to the left of it. To the right of that enter another formula. 5 columns to the right of Quads (AB2 in this example): =COUNTIF($AA$2:$AA$3431,AA2) and fill this down into the same row as the range of formulas to the left of it. Then enter dummy field names like A and B in AA1 and AB1, respectively. Select AA1:AB3431 and run an advanced filter (Data Filter Advanced Filter...), select 'Copy to another location', check 'Unique records only', and in the 'Copy to' field select yet another range, and click OK. When the result will be the 4-ball combinations in the left column and the number of times they appeared in Samples in the right column. Knowing how to do this stuff is possibly valuable. Spending time doing this sort of stuff to play lotteries is a monumental waste of time. Find the most frequent 'quad' in the first half of your samples, then see how often it appears in the second half. Note: if you're thinking about playing the most frequent 'quad' along with all other combinations of 2 numbers, be aware that that'd require 990 sets of numbers (45 choose 2 = 45 * 44 / 2). Your odds of winning by doing this would be slightly worse than 1 out of 14,125. -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Quad combinations
Dearest Mr. Grove,
I am eternally grateful for all your assistance and i'm humbly working through your latest formulae. As a senior contributor who views my endeavors as absurd and futile, when I hit the Big-one, can I presume you will be presented with an ethical dilemma on whether or not to forgo your 10% commission? ;) Thanks once again. Samuel. Please remove obvious spam trap if u wish to reply by email. |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Quad combinations
" wrote...
... As a senior contributor who views my endeavors as absurd and futile, when I hit the Big-one, can I presume you will be presented with an ethical dilemma on whether or not to forgo your 10% commission? ;) ... Tell ya what, put 1% of your lottery spending into an escrow account for my benefit. If you bankrupt yourself before winning the big one, I get the money. If you do win, you get it. Certainly someone with such accumen as you possess could see such a gamble is virtually a guaranteed winner for you. Lightning strikes people all the time, and not because they consciously chose to stand in the wrong place. Purely fortuitous good things happen too, even to people who believe they caused their own good fortune. If lotteries were predictable, there's be very, very few fully employed mathematicians and statisticians. Consider the logical implications of this. -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Quad combinations
Did you mean acumen?
Mr Grove, if you're going to post condescending retorts, you should at least get the spelling right; misspellings tend to throw the reader off the content. Samuel. Please remove obvious spam trap if u wish to reply by email. |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Quad combinations
misspellings tend to throw the reader
off the content. Apparently. Seems to me, Harlan invested some time answering your original question and provided some sage advice at the end. You are the one that baited him. In fact, if Harlan correctly interpreted your strategy, you are decreasing your chances. As an example, Assume 4 letters A, B, C, D with 3 letter combinations being chosen in a drawing. The combinatorial possibilities a ABC ABD ACD BCD Assume 3 drawings have been held and the results were ABD ACD ABC As we know, each combination will, over time be chosen an equal number of times. Picking a combination containing A, your strategy, would actually lessen your chances, since 3 successes have already been "consumed." of course, unless the selection system is flawed, each drawing result is independent of past experience, but if one believes an infinitesimal gain can be made, you should do the opposite of the most frequently selected quad. -- Regards, Tom Ogilvy wrote in message om... Did you mean acumen? Mr Grove, if you're going to post condescending retorts, you should at least get the spelling right; misspellings tend to throw the reader off the content. Samuel. Please remove obvious spam trap if u wish to reply by email. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 runs slow after upgrading CPU from dual to quad core | Excel Discussion (Misc queries) | |||
Quad processor & Excel 2007 | Excel Discussion (Misc queries) | |||
Sum of combinations | Excel Discussion (Misc queries) | |||
Possible Combinations | Excel Discussion (Misc queries) | |||
getting combinations | New Users to Excel |