All Permutations of Three Variables

[DRSteele]

DRSteele submitted a new Excel article:

All Permutations of Three Variables - Using Excel's new dynamic array formulas, we can easily generate all permutations of three variables

In order to generate all the permutations for three variables, put the variables in an Excel Table (which is called Inventory here). You can add as many rows to the Table as desired and you can fill in all or just one of the rows for each variable. The exemplar here will spill down 60 rows, the number of permutations for this 4_3_5 = 60 setup.

These formulas rely on Excel's new dynamic calculation engine and powerful array functions (like SEQUENCE).

If you can figure out how to set this up...

Read more about this Excel article...

 

[StephenCrump]

If you can figure out how to set this up for more than three variables, go ahead and share it with us!

ColTotals: =MMULT(SEQUENCE(,ROWS(Inventory),,0),--NOT(ISBLANK(Inventory)))
(If would be nicer if you could use Dynamic Arrays to calculate row and column totals!)

Permutations: =PRODUCT(ColTotals)

This readily accommodates expanding the Inventory table to 4,5,6+ ... columns - simply insert a column, populate with values, and copy the NoRows formula to the new column.
You can also delete down to 2 or 1 columns without needing to make any changes.

Permuations - dynamic.xlsx
	H	I	J	K	L	M	N	O	P	Q
5								Case	Lot	Shelf
6	Case	Lot	Shelf					A	w	1
7	A	w	1					A	w	2
8	B	x	2					A	w	3
9	C	y	3					A	w	4
10	D		4					A	w	5
11			5					A	x	1
12								A	x	2
13	15	5	1					A	x	3
14								A	x	4
15								A	x	5
16								A	y	1
17								A	y	2
Sheet1

Cell Formulas
Range	Formula
O6:Q65	O6	=INDEX(Inventory,MOD(INT(SEQUENCE(Permutations,,0)/NoRows),ColTotals)+1,SEQUENCE(,COLUMNS(Inventory)))
H13:J13	H13	=Permutations/PRODUCT(IF(SEQUENCE(,COLUMNS(Inventory))<=COLUMNS($H13:H13),ColTotals,1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
NoRows	=Sheet1!$H$13:$J$13	O6, H13:J13

Note that the formula in H13 is copied across, not a dynamic array. I can't see an easy way to calculate cumulative products.

 

[DRSteele]

That's an interesting solution, Stephen. Thanks.

I really hope MS enhances the dynamic formula technology soon to allow this kind of operation. We really just want the formula to spill when new columns are added.