So there are 15 games left in the NCAA tournament, making 2^15=32,768 possible outcomes. How would you go about creating a spreadsheet in EXCEL that would create every scenario as a 1 if the top team wins (i.e the team listed on the top of the bracket) and a 0 for the lower team. So you basically would have 32k+ rows, 15 columns that basically look like:
111100011010001
111110011010001
111111011010001
etc.
Is there a tricky/macro way to do this?
According to your system, the “number” for each outcome would go from:
000000000000000 to
111111111111111.
There is a command (at least listed in Excel help for Office 2003) called dec2bin. It doesn’t seem to be installed on my machine, but that would sound like a place to start. Also, it seems that you want to be able to have the spreadsheet be (somewhat) eaily readable. If you define the first eight digits (from the left) to be the first eight games, respectively, that should give you 2^8 or 256 possible outcomes for the first round. I would make these the first in each row, (e.g. 000000000000000 to 111111110000000, increasing by 256), with the numbers following (in each row) increasing by 1. Do a dummy line of decimal in the a col, us the b col to convert to decimal, then the c thru (whatever) col as one more than the previous cell.
I don’t want to insult you here, so please forgive me if you know this, but to make a formula apply to a new cell, do you know that you can highlight the first box (with the correct fomula), then click on the little square in the outline of the box and drag it? Outta cut your time down by a lot.
Good luck
I was playing around with dec2bin and it seems limited to 11111111 (511). Is there any trick to getting beyond this?
Write a custom function. If you are unfamilliar with how to do this, go to Tools…/Macro/Macros… Enter the name of the custom function , let’s call it BetterDecToBin, and click the create button.
In the Visual Basic edit window that comes up, enter the following code (replacing any code you see there):
Public Function BetterDecToBin(a As Integer) As String
Dim ix As Integer
Dim sBin As String
For ix = 14 To 0 Step -1
If a And 2 ^ ix Then
sBin = sBin & "1"
Else
sBin = sBin & "0"
End If
Next
BetterDecToBin = sBin
End Function
You should the be able to use the BetterDecToBin like any other Excel function in a cell formula.
I’m not sure if all editions of Excel allow you to create/edit custom VB macros , so this may not work for you.
Nitpick: 11111111 (eight ones) is 255.
I think you could get around this if you can figure out a way to (a) do integer division in Excel and (b) concatenate the results. Then you just need to:
[ol]
[li]Divide the number by 256, and put the quotient into one column and the remainder in another. (You might try looking up the FLOOR and MOD functions.)[/li][li]Convert each column into binary.[/li][li]Say hi to Opal.[/li][li]Concatenate the two results.[/li][/ol]
So, for example, 255 would be mapped to 0 and 255, which would be mapped to 00000000 and 11111111, which would be mapped to 0000000011111111. On the other hand, 256 would be mapped to 1 and 0, which would be mapped to 00000001 and 00000000, which would be mapped to 0000000100000000. This should be sufficient to get all 2[sup]16[/sup] possibilities. You could probably combine all of the above steps (well, maybe not the third) into one formula, if real estate is at a premium.
I can’t do anything right today. 111111111 (nine ones) is 511 which is the limit that I’m reaching. It doesn’t really change much in the scheme of things.
Heh, just for the sheer amusement value, I gave it a try, using Mike H’s code. (works perfect, btw!)
Due to the maximum sheet size allowed in excel, this will take two pages: one for the decimal numbers and one for the binary. Do column A like I said, and columns B though IV as one more than the cell to their left. Your numbers should go from 0 to 32767. Create the macro, just as Mike H says; then, on the next sheet, use the macro, but reference the cell in the same spot but on the previous sheet.
Man, is that ugly. Why’d you want to do this, Gangster?
My guess is for tracking a large office pool.