excel spreadsheet design for calculating occurrence in each of the 211876 combination

**computeruser08** · 01-29-2009, 11:25 PM

Hi

I have a dataset storing 6 numbers in a row (where each number range from 1 to 50 and no duplication of number within each row). Totaling there is 1000 rows.

e.g.
Col A B C D E F
1 2 11 12 49 50
1 2 21 22 31 32
1 2 21 22 23 24
3 6 9 15 16 17

I am going to build a spreadsheet showing all the possible combination (there are total 211876 combinations) and check the frequency of each combination draw from the database.

how should i set my spreadsheet to make it easier to set formula / make it able to drag across column & rows?

appreciate your help. currently i am only thinking to use countif function with 1st worksheet showing occurrence of each number (from 1 to 50),
then setup 2nd worksheet to chainlink the different combination of each number in 1st worksheet to check occurence,
then creating a 3rd spreadsheet to chainlink the combination of 2 figures....
and keep doing.....

**shg** · 01-30-2009, 12:23 AM

(there are total 211876 combinations)

For 50 numbers choose 6 there are 15,890,700 combinations.

Please explain again what you want to do.

**computeruser08** · 01-30-2009, 12:29 AM

i know to choose a combination of 6 numbers will be too much combination.
so i decided to test only a 4-number combination which from formula, 49! / ( 4! * 45!) gives 211876 combination. i said from 1 to 50 just for avoidance of confusion, which now seems more confusing.

i want to test if there is any statistical relationship between each combination. so i first i have find the frequency of happening in each combination.

**shg** · 01-30-2009, 12:33 AM

For 50 choose 4 there are COMBIN(50,4) = 230,300 combinations. Do you want to list those combinations?

**Armando Montes** · 01-30-2009, 01:04 AM

Check this one: http://www.savefile.com/files/518493

**computeruser08** · 01-30-2009, 02:33 AM

Thanks shg and Armando Montes. the combination excel is great!

But now with the list of combination and my dataset, how should i structure to do the checking of frequency? since data set is having each number in one cell while the list of combination is each combination in one cell.

i have attached the excerpt of the two in my excel. thanks.

**shg** · 01-30-2009, 09:42 AM

Post a manually-created example of what you want, and explain how you did it.

**computeruser08** · 01-30-2009, 10:58 AM

i have attached a sample.

i have a database sheet containing data starting from row 2. each row is one set.

then on 1-fig sheet, i checked if 1 is included in the each set, then checked if 2 is included in each set, then checked if 3 is included in each set.....so on and so for....

then on 2-fig sheet, i "try" (thinkking a better way to do the linkage/automation) to combine what i have on 1-fig to checked if 1,2 is included in each set, then checked if 1,3 is included in each set....so on and so for....

the combination list is just containing the combination i want to checked using the above calculation.

if use my above calculation, it will be very time consuming....do u think there is a better waythanks.

**shg** · 01-30-2009, 11:21 AM

You could skip the second tab, and on the third tab use two header rows (one for each of the numbers):

Please Login or Register  to view this content.

The formula in A3 and copied right and down is

=ISNUMBER(MATCH(A$1, database!$A2:$F2, 0)) * ISNUMBER(MATCH(A$2, database!$A2:$F2, 0))

**computeruser08** · 01-31-2009, 08:52 AM

thanks shg!

excel spreadsheet design for calculating occurrence in each of the 211876 combination

LinkBack

Thread Tools

Rate This Thread

Display

excel spreadsheet design for calculating occurrence in each of the 211876 combination

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Re: excel spreadsheet design for calculating occurrence in each of the 211876 combina

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions