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