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