Excel 2024: Generate All Combinations Using BASE Function

I occassionaly have a model where I need to test all possible combinations of several outcomes. For example, lets say that you have four different manufacturing lines and three scenarios that could happen per line.

You need a fast way to generate rows with 1-1-1-1, 1-1-1-2, 1-1-2-1, 1-1-2-2, and so on all the way down to 3-3-3-3.

Calculate how many rows you will have. The first manufacturing line has 3 possible outcomes. Multiply this 3 by the 3 possible outcomes for Line 2. Continue and you will end up with 3^4 or 81.

Long ago, there were functions in Excel to convert from Decimal to Binary to Octal, or Hexadecimal numbering systems. In Excel 2013, Microsoft improved those with the new BASE function. To see the number 49 in Binary, you could use =BASE(49,2). If you want to make sure that the resulting number has 8 digits, specify 8 as the minimum length.

The BASE function handles any numbering system from Base-2 to Base-36. For me, I rarely use it since all of my accounting happens in Base-10. However, there is a cool use for the BASE function when you need to generate all possible outcomes.

In the simple model below, you enter the number of columns and choices per column. Cell A5 calculates the number of combinations.

The action starts happening in cell A7. Working from the inside, you generate the numbers 0 to 80 using SEQUENCE(A5,1,0). Send this array into the BASE function for a numbering system of 3 with a minimum sequence of 4. This generates the combinations using the digits 0, 1, and 2. This formula would generate from 0000 to 2222. Since you want 1111 to 3333, you simply add 1111 to the result of the formula.

Note that I've hidden many rows in the middle here so you can see the beginning and end of the results.

A second formula in B7, copied down to all rows will break the four digits out into columns. This uses the MID function, with a SEQUENCE of 1 row by 4 columns to generate the numbers 1, 2, 3, 4. Copy this formula to the bottom of the array in column A.

---

---

This brand new feature is annoying half of all Excellers. It all depends on how you edit a formula. I edit by pressing F2 and editing right in the cell. But apparently a lot of people still click up in the Formula Bar. When you do, you get a dark box around the Formula Bar. How do you make it go away? You can't make it go away. What's going on? It's something called the F6 Loop. It's an accessibility feature that jumps around the screen when you press F6.