calculate combinations

[Peter1973]

I am trying to find a way of calculting every possible combination from the following :

In 199 cells each one can be a time of day in 15 min increments ie 12:00 13:15 13:30 22:45 and so on.

Is there any formula or way which will be able to represent evey possible combination of 199 times in the 15 min increments.

Any help on this appreciated.

 

[Beate Schmitz]

Hello Peter,

do you want by chance one of all possibilities, then this formula:

Excel Workbook
	A
1	11:30
Sheet

If you want a list of all possibilities then this way:

Excel Workbook
	A
1	00:15
2	00:30
3	00:45
4	01:00
5	01:15
6	01:30
7	01:45
8	02:00
9	02:15
10	02:30
Sheet

Formula of A1 can be copied down.

 

[Peter1973]

This gives me 1 list of a possible combination but how do i get it to give me a list containing every possible combination of 199 cells with times in them in any combination of 15 min increments.

 

[Beate Schmitz]

Hello Peter,

you want 199 cells including all possible combinations. There are, however, only 96 different combinations.

One Proposal:

Put this formula in A1 and copy down to A96:

Excel Workbook
	A
1	0:15
Sheet

Put this formula in cell A97 and copy down to A199:

Excel Workbook
	A
97	14:30
Sheet

Change formulas to values.

Put following formula into cell B1 and copy down to B199:

Excel Workbook
	B
1	0,70534097
Sheet

Change formulas to values in column B and sort values according to column B upwards. Then you are sure that every possible value is at least one time in range A1:A199 and they are unsorted. Afterwards you can delete column B.

 

[Peter1973]

There are a lot more than 96 combinations for example 1 to 199 could eqal 00:30 then 1to 198 could = 00:30 then 199 could equal any time from 00:00 to 23:45 in a 15 min increment then 1 to 197 could = 00:30 with 198 being any time and 199 = 00:30. As there are 199 cells each of whic could contain any time in the 24 hour clock in a 15 min increment there will be thousands of combinations.

 

[pgc01]

There are a lot more than 96 combinations for example 1 to 199 could eqal 00:30 then 1to 198 could = 00:30 then 199 could equal any time from 00:00 to 23:45 in a 15 min increment then 1 to 197 could = 00:30 with 198 being any time and 199 = 00:30. As there are 199 cells each of whic could contain any time in the 24 hour clock in a 15 min increment there will be thousands of combinations.

Hi Peter

Would you care to explain what you're trying to do. It's not feasible to calculate what you want. From your last post it seems you want any of the 199 numbers to have any of the 96 15 minutes interval. That's not combinations, that's permutations with repetition.

The total number of permutation would be something like 10^220. This means that if you used all the computers in the world you would not have space to store them (not even close). Also the age of the universe would be a small (tiny) amount of time compared to the time to calculate these permutations.

In conclusion: if you explain your problem, maybe we can help with suggestions.´