Using Excel to generate all possible conditions
I was wondering can I use Excel to autopopulate a grid of all possible states for a set of conditionals?
Starting simply, lets say I have 2 conditionals, each of which can be true or false. I want a sheet that shows all of these in a grid so I can go ask people (show them) what happens in each state of the program.
So the grid would look like:
func1(var1)  func1(var2)
0 0
1 0
0 1
1 1
I actually have 6 conditionals all that can be true or false. I know some cases are just invalid, while others will need an action. Is there any way to get excel to make this grid for me? Ot do I need to roll my own program and if so how would I do it?
So with some quick math it looks like I have 90 possibilities. I used 6!/2!(62) to get that number, it seems right, but god knows it might be wrong.
Any help is greatly appreciated.
Jeff
Friday, April 11, 2003
You can write VB (actually VBA) code in excel to make it do pretty much whatever you want. Choose Tools > Macro > Visual Basic Editor from the menu. Then, when the editor opens up, you can use the help system to get VB language help.
Benji Smith
Friday, April 11, 2003
I think excel comes with the permutation calculation in the functions. I haven't used excel for a long time but there used to be an analysis plugin.
bdw
Friday, April 11, 2003
First of all, you have 2^6 possibilites (64).
Tools / AddIns and install the Analysis Toolpak.
Enter the numbers 063 in column A by entering 0 and 1 and dragging down the fill handle.
In B1 enter the formula
=MID(DEC2BIN($A1,6),COLUMN()1,1)
Select B1:G1 and hit Ctrl+R
Select B1:G64 and hit Ctrl+D
Joel Spolsky
Friday, April 11, 2003
Jeff  you might wan to check your math. By my calculations, you have 64 possibilities (2*2*2*2*2*2) = 2^6 = 64.
Sergent Sausage
Friday, April 11, 2003
Joel's method uses the fact that binary numbers inherently give all the possible true/false combinations. Quite elegant, but if it loses you, just start small and build up. Enter the 2x4 truth table in, say, A2:B5. (You know, True True in the first row, True False in the second, then False True, then False False.) Copy and paste it into A6:B9, and add the third column of values ('True' in C2:C5, 'False' in C6:C9). You now have the 3x8 truth table. Rinse and repeat until you get six columns and 64 rows.
(The formula you were using, N!/K!(NK)!, [note the last exclamation point, which you missed] gives the number of ways to choose a subset of K objects from N different objects, where the internal ordering in K doesn't matter. In your case, the combination of 6 over 2 gives 15. Not a very useful number here, because you don't have 6 different objects.)
Martha
Friday, April 11, 2003
Another option is using Access, just create a table with 1 field containing 2 records, record 1 has value 0 and record 2 has value 1.
Then create a query that returns the cartesian product (no join) of the original table plus 5 identical ones (using alias names).
For example, the sql for 2 conditionals would be:
SELECT Table1.field, Table1_1.field
FROM Table1, Table1 AS Table1_1;
Al
Friday, April 11, 2003
Thanks for the suggestionsand corrections. This will help a lot.
Jeff
Saturday, April 12, 2003
You can hight light the code below, and then do a ctrlc (copy):
Then, launch Excel.
Hit altf11
Double click on sheet1
Hit ctrlv to paste my code
Hit f5 to run.
Done…
Sub MakeBinGri()
Dim intCols As Integer
Dim intRows As Integer
Dim intRowPtr As Integer
Dim intColPtr As Integer
Dim strText As String
Const intOffset As Integer = 3
intCols = Val(InputBox("Enter number of collumns", "Cols?"))
If intCols = 0 Then
Exit Sub
End If
intRows = (2 ^ intCols)  1 ' zero based
intCols = intCols  1 ' zero based
For intRowPtr = 0 To intRows
For intColPtr = 0 To intCols
If (intRowPtr And (2 ^ intColPtr)) > 0 Then
strText = "True"
Else
strText = "False"
End If
Cells(intRowPtr + intOffset, intColPtr + intOffset) = strText
Next intColPtr
Next intRowPtr
End Sub
Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
Albert D. Kallal
Monday, April 14, 2003
It is just transforming to base 2, no need for VBA here:
Enter in A2=0,A3=1, ...,A65=63
Enter in B1=5,C1=4,...,G1=0
Enter in B2: =INT(I2/POWER(2,B$1))
Drag this formula to all the cells in the B2G65 rectangle
Enter in I2: =MOD($A2,POWER(2,B$1+1))
Drag this formula to all the cells in the I2N65 rectangle
Your results are now in B2G65
Just me (Sir to you)
Monday, April 14, 2003
Thanks for all the help, it worked out great. That and I now have another subject to brush up on... stats. I have tried most of the solutions offered just to learn some more about Excel. Some pretty slick stuff, never realized Excel could do so much before I started reading its help file.
Thanks again...
Jeff
Monday, April 14, 2003
Recent Topics
Fog Creek Home
