To calculate every base asset inside nested creative kits, separate direct assets from kit contents, multiply through each nesting level, and add the results. This Excel for Mac walkthrough builds a complete requirements matrix and an exact asset-and-kit lookup.
The task is to find how many instances of each graphic are required for one kit—or a chosen batch of kits—when kits contain other kits. All labels and quantities below are synthetic. The calculation counts graphic instances; it does not deduplicate reusable source files.
Which Excel for Mac versions support this workflow?
Use Excel for Microsoft 365 for Mac, Excel 2024 for Mac, or Excel 2021 for Mac. Microsoft lists these editions for the matrix and lookup functions used here. Microsoft also explicitly documents dynamic arrays in Excel 2021 for Mac. The walkthrough uses ordinary worksheet ranges and formulas confirmed with Enter. MMULT function, INDEX function, What’s new in Excel 2021 for Mac.
A dynamic array formula fills neighboring cells automatically, a behavior called spilling. Enter each array formula only in its specified starting cell, and leave its output range empty. Keep these formulas outside Excel Tables: Microsoft documents that spilled array formulas are not supported inside Tables. Dynamic array formulas and spilled array behavior.
The worked arithmetic was checked with a small deterministic Python calculation. The formulas were not executed in the Excel desktop application.
How should you record direct assets and nested kits?
Start with a blank worksheet. You will create two input matrices: one for base graphics placed directly into each kit, and another for kits placed directly inside other kits. Keeping these inputs separate prevents already-expanded quantities from being counted again.
Enter the direct base-asset matrix
Paste this tab-separated block into A1. Its numeric area, B2:D5, has four asset rows and three kit columns.
Asset DotKit StripeKit StudioKit
Dot 2 1 0
Arc 1 0 2
Grid 0 3 1
Tile 0 1 0
Read each column as the direct graphic contents of one kit. One DotKit directly contains two Dot instances and one Arc. One StripeKit directly contains one Dot, three Grid instances, and one Tile. Its nested kits will be recorded separately.
Use a numeric zero for every absent component. Microsoft specifies that MMULT inputs must contain numbers; empty cells or text can produce #VALUE!. Keep the labels outside the numeric ranges passed to the function. MMULT function.
Enter the kit-within-kit matrix
Paste the following block into A7. Here, a row identifies the component kit and a column identifies the parent kit. The numeric nesting matrix occupies B8:D10.
Component kit DotKit StripeKit StudioKit
DotKit 0 2 1
StripeKit 0 0 3
StudioKit 0 0 0
The matrix describes these recipes:
- DotKit contains no other kits.
- StripeKit contains two DotKits.
- StudioKit contains one DotKit and three StripeKits.
Record only immediate contents. StudioKit’s one direct DotKit belongs in D8; the six additional DotKits inside its three StripeKits must be calculated. Entering seven in D8 would count those six twice.
Use exactly the same kit order across B1:D1, B7:D7, and A8:A10: DotKit, StripeKit, StudioKit. Matrix multiplication matches positions, so matching labels alone will not repair a differently ordered input.
This example assumes nonnegative whole-number quantities and recipes that eventually reach base assets. No kit may contain itself through a chain of other kits.
How do you calculate the complete requirements matrix?
Call the direct asset matrix B and the nesting matrix N in the explanation below. These are mathematical labels, not workbook names you need to define. Multiplying B by N gives the assets contributed through one kit-containment link. Multiplying that result by N adds another link.
For these three kits, the deepest chain is StudioKit containing StripeKit containing DotKit. Consequently, the complete matrix is the sum of direct assets, assets one nesting level down, and assets two levels down:
Complete requirements = B + BN + BN²
The superscript denotes repeated matrix multiplication. In Excel, calculate it with MMULT; applying the ordinary ^2 operator to a range would square its individual entries instead.
Calculate the contributions at each nesting level
Enter this formula in G2, then press Enter. It fills G2:I5 with the assets reached through one nesting link.
=MMULT($B$2:$D$5,$B$8:$D$10)
Enter the next formula in L2. It fills L2:N5 with the assets reached through two nesting links.
=MMULT(G2:I5,$B$8:$D$10)
The dimensions explain the multiplication order: a four-by-three asset matrix multiplied by a three-by-three nesting matrix produces another four-by-three asset matrix. Microsoft documents that the first array’s column count must equal the second array’s row count. MMULT function.
For an intermediate check, the StudioKit column of G2:I5 should contain 5, 1, 9, and 3. Its column in L2:N5 should contain 12, 6, 0, and 0, following the asset order Dot, Arc, Grid, Tile.
Add the direct and nested contributions
Enter DotKit, StripeKit, and StudioKit across Q1:S1. Enter Dot, Arc, Grid, and Tile down P2:P5. Then put this formula in Q2:
=$B$2:$D$5+G2:I5+L2:N5
The completed matrix in Q2:S5 should be:
| Base asset | DotKit | StripeKit | StudioKit |
|---|---|---|---|
| Dot | 2 | 5 | 17 |
| Arc | 1 | 2 | 9 |
| Grid | 0 | 3 | 10 |
| Tile | 0 | 1 | 3 |
Check StudioKit’s Dot quantity manually. It has zero direct Dots, two from its direct DotKit, and fifteen from three StripeKits containing five Dots each. The total is seventeen. This check follows every route to the same asset, including routes through a shared subkit.
How can you prove that no nesting level is missing?
A plausible total does not establish that the expansion is complete. Use the nesting matrix itself to check whether another containment level exists.
Enter this formula in G8 to calculate the two-link nesting matrix in G8:I10:
=MMULT($B$8:$D$10,$B$8:$D$10)
Then enter this formula in L8 to calculate the three-link nesting matrix in L8:N10:
=MMULT(G8:I10,$B$8:$D$10)
For the supplied data, G8:I10 contains one nonzero entry: six DotKits reached through StripeKit inside StudioKit. Every entry in L8:N10 is zero. There is no three-link route, so there can be no contribution beyond the two nesting levels already included.
This stopping rule is derived from the matrix model. With nonnegative quantities, an all-zero next nesting matrix means every longer nesting path is also absent. It is stronger than checking whether one particular asset happens to receive another contribution.
For a larger workbook with k distinct kits and no containment cycles, the longest possible chain has at most k−1 links. Continue multiplying each asset-contribution matrix by the nesting matrix and adding the results until the next nesting power is all zero. The three-term formula above is complete for this example; it must be extended when additional kits create deeper chains.
Ordering kits from simplest to most nested makes inspection easier. In the supplied nesting matrix, all nonzero entries sit above the diagonal. A positive diagonal entry means direct self-containment. If a longer chain returns to its starting kit, correct that recipe before using this finite expansion.
How do you look up one asset-and-kit quantity?
Set W1 to Asset, X1 to Kit, and Y1 to Quantity. Enter Arc in W2 and StudioKit in X2. In Y2, enter:
=INDEX($Q$2:$S$5,MATCH(W2,$P$2:$P$5,0),MATCH(X2,$Q$1:$S$1,0))
The result should be 9. The first MATCH locates the asset row; the second locates the kit column. INDEX returns their intersection. Microsoft documents this row-and-column selection behavior and confirms that MATCH with 0 requests an exact match without requiring sorted labels. INDEX function, MATCH function.
Keep labels unique within each axis because exact MATCH returns the first matching position. A missing label produces #N/A. Leave that error visible while checking names rather than replacing it with zero: zero already has a meaningful interpretation in the requirements matrix. MATCH function.
How do you calculate a batch of several kits?
Enter DotKit, StripeKit, and StudioKit across Q7:S7. Enter 2, 1, and 3 across Q8:S8. These are the independently requested quantities: two DotKits, one StripeKit, and three StudioKits.
Put Total instances in U1, then enter this formula in U2:
=MMULT($Q$2:$S$5,TRANSPOSE($Q$8:$S$8))
TRANSPOSE converts the horizontal three-kit demand row into a vertical column. The multiplication then produces one total per base asset, filling U2:U5. Microsoft documents TRANSPOSE as switching rows and columns. TRANSPOSE function.
| Base asset | Calculation | Total instances |
|---|---|---|
| Dot | 2×2 + 5×1 + 17×3 |
60 |
| Arc | 1×2 + 2×1 + 9×3 |
31 |
| Grid | 0×2 + 3×1 + 10×3 |
33 |
| Tile | 0×2 + 1×1 + 3×3 |
10 |
Do not add the internally required DotKits and StripeKits to this demand row. Their contents are already included in the complete matrix. Additional demand entries represent extra kits requested separately.
What should you check when the result looks wrong?
Work through the following checks before changing the formulas:
- An array shows
#SPILL!: inspect its intended output range for existing entries or merged cells. Move the obstruction or relocate the formula. Microsoft documents both conditions as spill blockers. How to correct a #SPILL! error. - A multiplication shows
#VALUE!: check for blanks, text, included headers, or incompatible dimensions in its inputs. MMULT function. - Totals are too high: check whether the direct matrices already include expanded subkit contents, or whether internal kit quantities were added to batch demand.
- Totals are too low: check the next nesting power. A nonzero result means the expansion may need another contribution level.
- Numbers look reasonable under the wrong headings: compare kit order across both input matrices and the output headers.
When extending the workbook, update every affected input range, helper calculation, output header, lookup range, and demand range. These formulas use fixed addresses; typing a new kit beside the existing inputs does not incorporate it automatically.
Keep the direct recipes as the editable inputs, retain the nesting-depth check beside the calculation, and spot-check one complete asset path after each structural change. With those checks in place, the finished matrix supports both individual kit lookups and batch totals from the same recorded recipes.
Sources
- MMULT function — Microsoft Support.
- What’s new in Excel 2021 for Mac — Microsoft Support.
- Dynamic array formulas and spilled array behavior — Microsoft Support.
- INDEX function — Microsoft Support.
- MATCH function — Microsoft Support.
- TRANSPOSE function — Microsoft Support.
- How to correct a #SPILL! error — Microsoft Support.