Pool Combination generator - excel 2000

groot2000 · Dec 13, 2005

Hi, I'm using excel 2000 and interested in getting some help regarding combinations.
I'm in a hockey pool and have the following games:
game 1: Team A vs Team B
game 2: Team A vs Team B
game 3: Team A vs Team B

I'm interested in determining a list of all the possible winning combinations. For example:
game 1: A (winner)
game 2: B (winner)
game 3: A (winner)

I realize that there are 8 different combinations (POWER(2,3)) or 2 to the power of 3.
What I'm wondering is if excel can generate the 8 different combinations in a list for me, similar to what I've shown above?
Thanks.

g

Greg Truby · Dec 13, 2005

I'd do a search on "List Combinations". Maybe even "pool" or "fantasy". This question has come up a time or two.

groot2000 · Dec 13, 2005

Yeah, I spent over an hour searching before posting my question.
While there was a lot of posts on fantasy sports leagues, there didn't seem to be anything regarding excel writing out the various combinations of winning teams.
Any other ideas?

Greg Truby · Dec 13, 2005

Most solutions posted tend to use VBA to iterate through nested For/Next Loops.

groot2000 said:
Any other ideas?

You could always break the problem into chunks.

First you need a formula that will toggle back and forth between 0 and 1 by row.
Then make it toggle between 1 and 2 by adding 1.
Then make it toggle every 2^n rows when moved column to column.
Then push that into a CHOOSE() formula to show team "A" or "B" [or use MATCH or Char(x+64) or Lookup()...pick your preference].

And Voilá!

book2