2. Dynamic array fundamentals for engineering datasets

Engineering workbooks rarely stay a fixed size. A vibration test produces 4,096 samples per channel, a materials lab logs a variable number of tensile specimens per batch, and a calibration table may need to be reordered by timestamp before it can be joined to a test log. Legacy Excel formulas handled this by filling a helper column down to a guessed maximum row and wrapping everything in IFERROR. Dynamic arrays replace that pattern with a single formula that returns a spilled range: one expression whose output automatically occupies as many cells as it needs, and shrinks or grows when the inputs change.

This section covers the mechanics that matter when the inputs are measurement tables rather than tidy business lists: how spill ranges behave and where they fail, how SEQUENCE and RANDARRAY generate index and sampling vectors, and how to reshape a wide or ragged measurement table into a form that downstream formulas can consume.

Spilled ranges: one formula, many cells

A dynamic array formula is entered in a single cell — the anchor — and its result spills into the neighbouring cells. The anchor holds the formula; the rest of the spill range holds values that Excel manages. Selecting any cell in the range draws a thin border around the whole block, and the anchor displays the formula in the formula bar. Two consequences follow immediately for engineering sheets:

• Downstream references must target the whole range, not one cell. If D2 contains =SEQUENCE(4096), then D2# (the spill operator) refers to all 4,096 values. Writing SUM(D2:D4096) works but breaks silently if the sample count changes; SUM(D2#) does not.
• The spill range must be empty. If any cell the result needs already contains data, Excel returns #SPILL! and nothing is written. This is the most common failure when a new dynamic formula is dropped into a legacy sheet that still has a filled helper column beneath it.

Spill ranges also propagate: a formula that references D2# becomes dynamic itself. This is what makes a chain of transformations practical — generate an index, use it to pull rows, reshape the result, and each stage resizes automatically when the source table gains a row.

SEQUENCE: generating index and coordinate vectors

SEQUENCE(rows, [columns], [start], [step]) returns an array of evenly spaced numbers. For engineering data the most valuable use is not counting but addressing: building row indices, time vectors, or a two-dimensional grid of test-point coordinates.

=SEQUENCE(4096)                  → 1, 2, 3, … 4096   (sample index)
=SEQUENCE(4096, 1, 0, 1/1000)     → 0, 0.001, 0.002, … (time vector at 1 kHz)
=SEQUENCE(8, 6)                   → 8×6 grid of 1…48  (test-point map)

The time-vector example is worth pausing on. If a data-acquisition system sampled at 1,000 Hz for 4,096 points, the correct time axis is SEQUENCE(4096,1,0,1/1000), giving 0 s to 4.095 s. Hard-coding a column of timestamps is fragile: change the sample rate and every value must be regenerated. With SEQUENCE, the rate lives in one cell and the vector follows it. A common engineering pattern is to keep fs in a named cell and write =SEQUENCE(n,1,0,1/fs), so the axis and the sample count are both driven by parameters rather than typed constants.

SEQUENCE also pairs naturally with INDEX to reverse or reorder a measurement column without helper cells: =INDEX(B2:B4097, SEQUENCE(4096,1,4096,-1)) returns the samples in reverse order, which is useful when a log was written newest-first and a downstream calculation expects chronological order.

RANDARRAY: sampling, shuffling, and synthetic test data

RANDARRAY(rows, [columns], [min], [max], [whole_number]) produces random values in a spilled range. Two engineering uses dominate. The first is Monte Carlo input generation: to study how a tolerance stack-up behaves, generate 10,000 draws of a dimension and feed them through the assembly formula.

=RANDARRAY(10000, 1, 49.95, 50.05)        → uniform draws, mm
=NORM.INV(RANDARRAY(10000,1), 50, 0.02)    → normal draws, mean 50, σ 0.02

The second is randomised ordering for validation: attach a random key to each row, sort by it, and you have a shuffled copy of the measurement table for testing whether a lookup or join is order-dependent. RANDARRAY is volatile — it recalculates on every sheet change — so freeze a Monte Carlo run by copying the spill range and pasting as values before you report results. Otherwise the numbers you quote will not match the numbers on screen when the workbook is reopened.

Reshaping measurement tables

Measurement exports are often wide: one row per test, one column per channel or per specimen, with a header row of channel names. Analysis formulas usually want long form — one row per observation, with columns for test ID, channel, and value. Dynamic arrays make this a formula rather than a manual copy-paste.

Consider a table in A1:E5 where column A holds test IDs (T-01…T-04) and columns B–E hold four channel readings. To unpivot into long form, generate the row and column indices with SEQUENCE, then index into the source:

Rows:    =SEQUENCE(ROWS(A2:A5)*COLUMNS(B1:E1), 1, 1, 1)
TestID:  =INDEX(A2:A5, INT((SEQUENCE(16)-1)/4)+1)
Channel: =INDEX(B1:E1, MOD(SEQUENCE(16)-1, 4)+1)
Value:   =INDEX(B2:E5, INT((SEQUENCE(16)-1)/4)+1, MOD(SEQUENCE(16)-1,4)+1)

Each of the four formulas spills 16 values, and the four spill ranges together form the long table. The integer division and modulo pair is the general recipe: INT((i-1)/n)+1 walks down the source rows, MOD(i-1,n)+1 walks across the source columns. Because every stage is driven by SEQUENCE, adding a fifth test or a fifth channel only requires updating the ROWS/COLUMNS arguments — or better, referencing the source ranges directly so the counts are computed.

For the reverse direction — long to wide — UNIQUE supplies the row and column headers and a two-dimensional lookup fills the body. If G2:G17 holds test IDs and H2:H17 holds channel names, then =UNIQUE(G2:G17) and =TRANSPOSE(UNIQUE(H2:H17)) build the header axes, and a SUMIFS or XLOOKUP keyed on both fields populates the grid. The result is a pivot-like summary that refreshes when the long table changes, without a PivotTable refresh step.

How the pieces connect

The diagram below shows the typical transformation chain for a measurement export: a parameter block drives index generation, the indices address the source table, and the reshaped output spills into a range that downstream statistics consume. Note that each stage references the previous stage's spill operator (#), so the chain resizes as a unit.

Practical checks before you build on a spill range

Three habits prevent most dynamic-array failures in engineering workbooks. First, anchor formulas in a dedicated output area — a sheet or block reserved for spilled results — so a later data paste cannot land inside an existing spill range and trigger #SPILL!. Second, reference spill ranges with # rather than fixed addresses, and verify by adding a row to the source and confirming the downstream result grows. Third, freeze volatile outputs: any RANDARRAY or RAND-based stage should be pasted as values before results are reported, with the generation parameters recorded alongside.

A quick verification sequence for a reshaped table: check that the row count of the long-form spill equals ROWS(source)×COLUMNS(source); spot-check two or three cells against the original wide table by hand; and confirm that SUM of the long-form value column equals SUM of the entire source data block. If the totals disagree, the index arithmetic in the INT/MOD pair is the first place to look — an off-by-one in the divisor produces duplicated or skipped rows that are easy to miss visually but show up immediately in the total.

With these mechanics in place, the reshaping formulas become reusable templates: swap the source ranges, keep the index logic, and the same pattern unpivots a calibration table, a multi-specimen tensile log, or a channel-by-channel vibration export without rewriting anything.