Using Excel to generate all possible conditions I was wondering can I use Excel to auto-populate 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!(6-2) 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 / Add-Ins and install the Analysis Toolpak. Enter the numbers 0-63 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!(N-K)!, [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 ctrl-c (copy): Then, launch Excel. Hit alt-f11 Double click on sheet1 Hit ctrl-v 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 B2-G65 rectangle Enter in I2:  =MOD(\$A2,POWER(2,B\$1+1)) Drag this formula to all the cells in the I2-N65 rectangle Your results are now in B2-G65 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   Fog Creek Home