09-04-2008, 02:23 PM | #1 | ||
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
Excel Help
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. |
||
09-04-2008, 03:02 PM | #2 |
College Prospect
Join Date: Oct 2000
Location: The Flatlands of America
|
Do you mean like using the Text-to Column function?
Select Text-to Columns -> Fixed Width -> Click between each number
__________________
Post Count: Eleventy Billion - so deal with it! |
09-04-2008, 03:28 PM | #3 |
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
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.
|
09-05-2008, 08:21 AM | #4 |
College Prospect
Join Date: Nov 2006
Location: High and outside
|
Essentially, those are binary. In the first column all the way down, =DEC2BIN(CELL("row")).
|
09-05-2008, 08:29 AM | #5 | |
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
Quote:
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. |
|
09-05-2008, 08:33 AM | #6 |
College Prospect
Join Date: Nov 2006
Location: High and outside
|
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). |
09-05-2008, 08:43 AM | #7 |
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
That could get me somewhere, thanks! I'll play around with that.
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|