All Permutations of Three Variables

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...

Click to expand...

Read more about this Excel article...

DRSteele said:

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

Click to expand...

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.



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

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.