Fog Creek Software
Discussion Board




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

*  Recent Topics

*  Fog Creek Home