Permutations in Excel

Question

Hello,

My situation is I am trying to know AND show all the options for contracting services.  The data set below has six (6) aspects.  The "option" is "Yes" or "NO".  For example, the first line below shows that all the aspects are of the option "NO".  The next option is all "NO" except the Revenue, etc...  I would like to get a "chart" showing all the permutations (I think that is what it is called) for the following:

		Registration	Brochure	Facility	Equipment	Expenses	Revenue
y = yes	Option 1	n	n	n	n	n	n
n = no	Option 2	n	n	n	n	n	y
	Option 3	n	n	n	n	y	y
	Option 4	n	n	n	y	y	y
	Option 5	n	n	y	y	y	y
	Option 6	n	y	y	y	y	y
	Option 7	y	n	n	n	n	n
	Option 8	y	y	n	n	n	n
	Option 9	y	y	y	n	n	n
	Option 10	y	y	y	y	n	n

etc...

I am trying to find out and show the not only the number of permutations but actually show what they are, as above.  

Is there a way to do this in Excel? or at all?

Thank you,

MJ

Answer 1

Hello,

I suggest NOT you use any worksheet function "solution" with an INDIRECT command in it. Please see my Excel Don't # 10:

http://sulprobil.com/Get\_it\_done/IT/Excel\_Fun/Excel\_Don-ts/excel\_don-ts.html

If I am not mistaken, all responses immediately assumed that you only have 6 variables with two values to permute on. I suggest NOT to restrict yourself too early and to implement a flexible approach with at least a minimum of VBA programming style.

Just for the fun of it I have added an additional value "dont know" for the variable Equipments (now showing "y", "n", and "dont know"). All of a sudden we got 96 permutations and guess which of all suggested approaches you can use now?

I have stored my sample file here:

https://dl.dropboxusercontent.com/u/6077606/Permutations\_in\_Excel.xlsm

(use my code at your own risk)

Option Explicit

Enum eColumns

    eCol_LBound = 1

    eCol_Option

    eCol_Reg

    eCol_Broch

    eCol_Fac

    eCol_Equip

    eCol_Exp

    eCol_Rev

    eCol_Ubound

End Enum

Sub CreatePermut()

    Dim vReg, vBroch, vFac, vEquip, vExp, vRev 'All variants

    Dim lRow As Long

    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    Range(ws.Cells.Columns(eCol_LBound + 1), ws.Cells.Columns(eCol_Ubound - 1)).Delete

    lRow = 1

    Range(ws.Cells(1, eCol_LBound + 1), ws.Cells(1, eCol_Ubound - 1)).FormulaArray = _

        Array("Options", "Registration", "Brochure", "Facility", "Equipment", _

        "Expenses", "Revenue")

    lRow = lRow + 1

    For Each vReg In Array("y", "n")

        For Each vBroch In Array("y", "n")

            For Each vFac In Array("y", "n")

                For Each vEquip In Array("y", "n", "dont know")

                    For Each vExp In Array("y", "n")

                        For Each vRev In Array("y", "n")

                            ws.Cells(lRow, eCol_Option) = "Option " & lRow - 1

                            ws.Cells(lRow, eCol_Reg) = vReg

                            ws.Cells(lRow, eCol_Broch) = vBroch

                            ws.Cells(lRow, eCol_Fac) = vFac

                            ws.Cells(lRow, eCol_Equip) = vEquip

                            ws.Cells(lRow, eCol_Exp) = vExp

                            ws.Cells(lRow, eCol_Rev) = vRev

                            lRow = lRow + 1

                            Debug.Assert lRow < 10000 'Rethink if breached

                        Next vRev

                    Next vExp

                Next vEquip

            Next vFac

        Next vBroch

    Next vReg

End Sub

Answer 2

With 6 aspects, you have to remember that you have 6*6=36 possible options.

Answer 3

Hi.  If 1/0 is ok, you can use powers of 2.

For example, Option 10 might be 60 decimal:

=DEC2BIN(60,6)

111100

Answer 4

Hi,

If you can live with the result being 1's and 0's instead of y's and n's, then here is my solution.  There will be 64 such permutations

1. In cell B2, type 6
2. In cell B3, type 2
3. In cell B4, type =B3^B2
4. In cell A7, enter this formula

=IF(ROW()-ROW($B$7)+1<=$B$4,1,"")

5. In cell A8, enter this formula and copy down till cell A70

=IF(ROW()-ROW($B$7)+1<=$B$4,A7+1,"")

6. In cell B7, enter this formula

=IF(A7<>"",IF(COLUMN()-COLUMN($B$7)+1<=$B$2,0,""),0)

7. In cell C7, enter this formula and copy till cell G7

=IF(B7="","",0)

8. In cell B8, enter this formula and copy till cell B70

=IF(A8<>"",MOD(B7+1,$B$3),0)

9. In cell C8, enter this formula and copy down/right

=IF(B8="","",IF(AND(B7<>B8,B8=0),MOD(C7+1,$B$3),C7))

Hope this helps.

Answer 5

Hi,

Let me rephrase your question, do you need a so called Boolean table?

In a reduced form, something like this where 1 stands for Yes/True and 0 for No/False.

	Registration	Brochure	Facility
Option 1	0	0	0
Option 2	0	0	1
Option 3	0	1	0
Option 4	0	1	1
Option 5	1	0	0
Option 6	1	0	1
Option 7	1	1	0
Option 8	1	1	1