Display all different combos

Hi,

I posted this in the general forum:

Hi,

I have a problem I'm trying to solve. The real problem is a bit hard to describe but one way is to compare it to stones that are to be placed in bags (Bag 1, Bag 2, Bag 3). Lets say I have three stones, then all the ways I can spread them out in the three bags are as follows:

Bag 1 Bag 2 Bag 3
3 0 0
0 3 0
0 0 3
2 1 0
2 0 1
1 2 0
0 2 1
1 0 2
0 1 2
1 1 1


And this is pretty quick to do manually. But if I have 8 stones and 4 bags (or even more stones or more bags). Then this exercise is a bit more time consuming.

Is there a way to use Excel 2013 to calculate and show all the different way to divided the stones?

Thanks in advance
Vic



Got this answer and it answers half of it:

This is combinatorics. I think I understand your question.

Think of it this way. You have an array and each element can either contain a stone or a divider to the next bag. That would require (stones+bags-1) elements to handle all possibilities. That is, you can split the stones up into the number of bags by using (bags-1) dividers. So, how many ways are there to place the (bags-1) dividers within that array of size (stones+bags-1)? =COMBIN((stones+bags-1),(bags-1))

Per your example above, but re-written with
o=stone
l=divider

oooll
loool
llooo
oolol
oollo
olool
loolo
olloo
loloo
ololo

For 3 bags and 3 stones: COMBIN(5,2) = 10. For 4 bags and 8 stones, COMBIN(11,3)=165.

As for showing them, that would require some VBA. You should post in that group. I'd probably use my example as a basis for an algorithm.
Last edited by Pauleyb; Yesterday at 11:08 PM.

Is there someone here that can help me show the in a spreadsheet?

Thanks in advance
Vic