DRSteele

All Permutations of Three Variables

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,667
Office Version
  1. 365
Platform
  1. Windows
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


Read more about this Excel article...
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
HIJKLMNOPQ
5CaseLotShelf
6CaseLotShelfAw1
7Aw1Aw2
8Bx2Aw3
9Cy3Aw4
10D4Aw5
115Ax1
12Ax2
131551Ax3
14Ax4
15Ax5
16Ay1
17Ay2
Sheet1
Cell Formulas
RangeFormula
O6:Q65O6=INDEX(Inventory,MOD(INT(SEQUENCE(Permutations,,0)/NoRows),ColTotals)+1,SEQUENCE(,COLUMNS(Inventory)))
H13:J13H13=Permutations/PRODUCT(IF(SEQUENCE(,COLUMNS(Inventory))<=COLUMNS($H13:H13),ColTotals,1))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
NoRows=Sheet1!$H$13:$J$13O6, 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.
 
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.
 

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top