# calculate combinations

#### Peter1973

##### Well-known Member
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hello Peter,

do you want by chance one of all possibilities, then this formula:
Excel Workbook
A
111:30
Sheet

If you want a list of all possibilities then this way:
Excel Workbook
A
100:15
200:30
300:45
401:00
501:15
601:30
701:45
802:00
902:15
1002:30
Sheet

Formula of A1 can be copied down.

Last edited:
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.

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
10:15
Sheet

Put this formula in cell A97 and copy down to A199:
Excel Workbook
A
9714:30
Sheet

Change formulas to values.

Put following formula into cell B1 and copy down to B199:
Excel Workbook
B
10,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.

Last edited:
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.

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.´

Replies
14
Views
693
Replies
7
Views
235
Replies
6
Views
201
Replies
2
Views
112
Replies
3
Views
251

1,203,189
Messages
6,054,021
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back