Excel Help

[Passacaglia]

More of a time-waster (football poll) then work-related, so not that important.

I've got this spreadsheet of everyone's football picks for our confidence pool week, and I'd like to see what happens in each possible combination of games. There are 2^16 combinations, which is the same number of rows you can put in an excel tab. Does anyone know a quick way I can make each row a separate combination? For example:

1010101010101010101010101010101010101010 -- away team wins every game
0101010101010101010101010101010101010101 -- home team wins every game

and so on? From there, it would be fairly simple to calculate everyone's score in each row.

[Scarecrow]

Do you mean like using the Text-to Column function?

Select Text-to Columns -> Fixed Width -> Click between each number

[Passacaglia]

I don't think so. I don't have any data at the moment -- I'm trying to create data containing every possible combination. My example above listed two rows out of the >60,000 that would be required. But that's just an example -- there's probably a lot of different ways this can be done. I'm basically just looking for any way I can look at a row of data, and know which combination I'm looking at, in a way that lets me add everyone's "confidence" number for each winner.

[Bobble]

Essentially, those are binary. In the first column all the way down, =DEC2BIN(CELL("row")).

[Passacaglia]

Originally Posted by Bobble

Essentially, those are binary. In the first column all the way down, =DEC2BIN(CELL("row")).

I was thinking of that..the problem is that the DEC2BIN function only works for numbers up to 511. For number above 511, I tried subtracting 511, then adding "11111111" (or however many ones there should be) to the left of what DEC2BIN gives me, and that gave me good results for numbers between 512 and 1023, but I'd have to do that a lot more times to get to 60,000.

[Bobble]

I didn't know about the restriction on DEC2BIN.

You could work with some intermediate columns like =INT(CELL("row")/511) to get how many times you have to add 511 (or whatever).

[Passacaglia]

That could get me somewhere, thanks! I'll play around with that.