 # Generating Permutations in Excel

Yesterday my brother was asking about generating permutations that could be used as an input to a project risking tool in Excel. It took me a little while to crack it (including some relatively fruitless Google searches 🙂 ) but the answer is really simple and elegant.

There are various uses for generating permutation runs–one of which I wrote a post on here about 6yrs ago! Mostly they are used for optimization problems. I had to dip into my stats book‘s first chapter to refresh my memory on ‘combinatorial methods’.

I wanted to find all possible sets of outcomes (call them runs) where there are 5 variables and 2 possible scenarios per variable (in this case the variables were oil_production, gas_production, capex, opex, abex; and the scenarios for each are low and high).

I started by drawing out the decision tree associated with the problem (see below).

Let’s refer to our variables as A,B,C,D,E and scenarios as 1 and 2. I realized that for each of the 2 possible outcomes of A, there are 2 possible outcomes for B (now, 4 combined possible outcomes). Then for each of those 4 outcomes, there are 2 possible outcomes for C (2*4=8 total outcomes). So I concluded that for x sets of y possible outcomes there are y^x potential runs.

### Getting Excel to write the Permutations

Now I needed to get Excel to write all these permutations (using VBA). The way I did this was to just use 5 (x=5 here, right) nested For-Next loops of 2 iterations (2 to 3 here) per loop with the output step located at the innermost loop.

The output is printed to the Immediate Window (Ctrl+G). But I have included the slightly altered file HERE that contains this code as well as some extra code to write the permutation runs to cells directly in Excel.

###### Excel Permutations Code (cut and paste to VBA Editor)
```Sub gpermuts() 'subroutine for generating all possible permutations of 5 sets of 2 outcomes
Dim v1, v2, v3, v4, v5 As Integer
For v1 = 2 To 3
For v2 = 2 To 3
For v3 = 2 To 3
For v4 = 2 To 3
For v5 = 2 To 3
Debug.Print v1, v2, v3, v4, v5
Next v5
Next v4
Next v3
Next v2
Next v1
End Sub```