Pool Combination generator - excel 2000

[groot2000]

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]

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

 

[groot2000]

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]

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

Any other ideas?

You could always break the problem into chunks.

1. First you need a formula that will toggle back and forth between 0 and 1 by row.
2. Then make it toggle between 1 and 2 by adding 1.
3. Then make it toggle every 2^n rows when moved column to column.
4. 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
	A	B	C	D
1	Winner G1	Winner G2	Winner G3	Winner G4
2	A	A	A	A
3	B	A	A	A
4	A	B	A	A
5	B	B	A	A
6	A	A	B	A
7	B	A	B	A
8	A	B	B	A
9	B	B	B	A
Sheet1

Formula in A2 and copied down and over is: <ul>[*]=CHOOSE(MOD(ROUNDUP((ROW()-ROW(A$1))/2^(COLUMN()-1),0)+1,2)+1,"A","B")[/list]