2. Dynamic array fundamentals for engineering datasets

Classic Excel formulas return exactly one value per cell. Dynamic arrays change that contract: a single formula can return a spilled range whose size is determined at calculation time by the data it references. For engineering work this is significant, because measurement tables rarely have a fixed row count. A sensor log may contain 4,800 samples today and 5,200 tomorrow; a test matrix may gain channels as instrumentation is added. Dynamic arrays let a summary block, a resampled grid, or a reshaped table resize itself automatically instead of being rebuilt by hand.

This chapter covers the mechanics of spilling, the two generator functions most useful for engineering data (SEQUENCE and RANDARRAY), and the reshaping functions that convert a flat measurement log into an analysis-ready matrix. The goal is a working mental model: you should be able to predict the shape of a result before you press Enter, and diagnose a #SPILL! error in seconds.

The spill contract: anchor cell, spill range, and the # operator

When a formula returns an array of m rows by n columns, Excel writes the top-left value into the cell containing the formula — the anchor cell — and fills the remaining cells automatically. That filled block is the spill range. The anchor holds the formula; the other cells hold results and cannot be edited individually. Selecting any cell in the block draws a thin border around the whole range, which is the fastest visual confirmation that a formula is spilling as intended.

To reference the entire spill range from another formula, use the spilled-range operator # appended to the anchor address. If F2 contains =SEQUENCE(10), then F2# refers to F2:F11 — and it stays correct if the generator later returns 12 rows. This is the key to building dependent calculations that do not break when the dataset grows. A chart series or a downstream statistic pointed at F2# will track the full output automatically.

SEQUENCE: generating index grids and resampling axes

SEQUENCE(rows, [columns], [start], [step]) produces an arithmetic progression. All four arguments are optional except rows. The default start is 1 and the default step is 1, so SEQUENCE(5) returns a vertical vector 1–5. Setting columns greater than 1 fills across first, then down — a detail that matters when you build a two-dimensional index grid.

A frequent engineering use is generating a uniform time axis for resampling. Suppose a vibration log is sampled at 0.5 ms intervals and you need a 1 ms grid for spectral analysis. With the sample interval stored in a named cell dt (0.0005 s) and the record length in N (4,800), the time axis is:

=SEQUENCE(N, 1, 0, dt)          ' 0, 0.0005, 0.001, ... seconds
=SEQUENCE(1, 8, 100, 50)         ' horizontal: 100, 150, 200, ... 450
=SEQUENCE(3, 4)                  ' 3x4 grid, values 1..12 filled row-wise

Because the step can be any real number, SEQUENCE also generates load steps, temperature setpoints, or bin edges. For bin edges, note the off-by-one trap: n bins require n+1 edges, so a 20-bin histogram needs SEQUENCE(21, 1, min, (max-min)/20). Getting this wrong shifts every bin boundary and silently distorts the distribution.

RANDARRAY: synthetic data and Monte Carlo scaffolding

RANDARRAY([rows], [columns], [min], [max], [whole_number]) returns an array of random values. With no arguments it returns a single value between 0 and 1. The whole_number flag (TRUE/FALSE) switches between continuous and integer output. Two properties deserve emphasis for engineering use. First, the values are volatile: they recalculate on every sheet change, so any downstream statistic flickers. Freeze a realization by copying the spill range and pasting as values. Second, RANDARRAY is uniform, not normal — for Gaussian noise you must transform it, for example with NORM.INV(RANDARRAY(n,1), mean, sd).

A practical pattern is a Monte Carlo tolerance stack-up. With nominal dimensions and tolerances in a small parameter block, generate 10,000 trials of each contributor, sum them across columns, and compute the fraction exceeding the specification limit. The generator line looks like:

=RANDARRAY(10000, 3, -1, 1) * tolerance_vector + nominal_vector

where tolerance_vector and nominal_vector are 1×3 ranges. The multiplication broadcasts the 1×3 vector across all 10,000 rows — a behavior worth verifying on a small case before scaling up.

Reshaping measurement tables

Instrument exports are usually long: one row per observation, with columns for timestamp, channel ID, and value. Analysis often wants wide form: one row per timestamp, one column per channel. Three functions cover most of this work.

• WRAPROWS(data, wrap_count) and WRAPCOLS fold a single vector into a matrix. Useful when a scanner writes a fixed number of readings per sweep and you want one row per sweep.
• TOROW and TOCOL flatten a block into a single vector, with options to scan by row or by column and to skip blanks or errors — handy for concatenating several channel columns into one clean list.
• CHOOSECOLS and CHOOSEROWS extract and reorder columns or rows by index, which is how you build a fixed analysis layout from a source table whose column order changes between instrument firmware versions.

For a true long-to-wide pivot without a PivotTable, combine UNIQUE on the timestamp column with a lookup per channel. If timestamps are in A2# and channel labels in B2#, the distinct time axis is =UNIQUE(A2#) and each channel column is a SUMIFS or XLOOKUP against that axis. The result is a live matrix that grows when new samples arrive.

Worked example: from raw log to resampled matrix

Assume a two-channel vibration log in A2:C4801 (time, channel, value) sampled at 0.5 ms, with the interval stored in dt and the record length in N. The following sequence produces a wide, uniformly gridded matrix. All values below are illustrative; verify against your own data.

1. Build the target time axis in F2: =SEQUENCE(N/2, 1, 0, 2*dt). Halving the row count and doubling the step resamples to 1 ms.
2. List the channels in G1:H1 as A and B.
3. In G2, enter =SUMIFS($C$2:$C$4801, $A$2:$A$4801, $F2#, $B$2:$B$4801, G$1) and fill right to H2. The $F2# reference makes each channel column exactly as tall as the time axis.
4. Confirm the spill border encloses G2:H2401. If it stops short, the axis and the lookup disagree on length — check the N/2 argument.

Two verification checks are worth building in. First, compare the row count of F2# against N/2 using =ROWS(F2#)=N/2; a FALSE result means the generator arguments drifted. Second, sum each channel column and compare against SUMIFS over the original long log — for a pure resample with no aggregation the totals should match within floating-point tolerance. A mismatch usually indicates duplicate timestamps or a channel label with trailing whitespace, which SUMIFS treats as a different channel.

The mental model to carry forward is simple: a dynamic array formula declares what to compute, and Excel derives how many cells from the inputs. Once you can predict the output shape from the arguments, spilling stops being surprising and becomes a design tool — one that keeps engineering summaries correct as measurement tables change size.