How to Use VSTACK and LAMBDA to Author Running Percentile Frequency Distributions in Microsoft Excel

Building statistical frequency distributions across progressive time series traditionally required complex pivot tables, manual helper columns, or volatile array combinations that required manual re-entry whenever datasets expanded. By combining dynamic array primitives like VSTACK, SCAN, and LAMBDA with the statistical PERCENTILE.INC and FREQUENCY functions, Microsoft Excel can generate fully automated running percentile distributions from a single top-level formula cell.

Understanding Running Percentile Distribution Architecture

A dynamic running frequency distribution evaluates numerical records incrementally as they accumulate over sequential periods, categorising observations into fixed percentile intervals:

• VSTACK: Joins summary headers, dynamically calculated frequency bins, and cumulative running totals into a unified, spill-compliant reporting matrix.
• SCAN: Iterates sequentially through progressive rows, calculating cumulative counts or rolling percentile thresholds without recalculating the entire historical series.
• PERCENTILE.INC: Determines boundary thresholds corresponding to designated quartile or decile tiers (e.g., 25th, 50th, 75th, and 90th percentiles).
• FREQUENCY: Returns the vertical array distribution of values falling within defined bin intervals.

Setting Up the Source Data and Bin Boundaries

Assume raw operational telemetry or revenue entries occupy column range A2:A100, with associated transaction timestamps in B2:B100:

1. Define fixed percentile bin markers in an adjacent range or inside an inline dynamic array constant:

   {0.25; 0.50; 0.75; 0.90}

2. Formulate target percentile values dynamically using PERCENTILE.INC:

   =PERCENTILE.INC(A2:A100, {0.25; 0.5; 0.75; 0.9})

Constructing the Complete Dynamic Formula

Enter the following unified formula into cell D2 to generate the complete frequency distribution table including header formatting:

=LET(
    data, FILTER(A2:A100, ISNUMBER(A2:A100)),
    tiers, {0.25; 0.5; 0.75; 0.9},
    thresholds, PERCENTILE.INC(data, tiers),
    freq_counts, FREQUENCY(data, thresholds),
    tier_labels, {"P0-P25"; "P25-P50"; "P50-P75"; "P75-P90"; "Above P90"},
    pct_share, freq_counts / COUNT(data),
    cum_share, SCAN(0, pct_share, LAMBDA(acc,val, acc + val)),
    table_body, HSTACK(tier_labels, freq_counts, pct_share, cum_share),
    header, {"Percentile Tier", "Frequency", "Share", "Cumulative"},
    VSTACK(header, table_body)
)

Formula Mechanics and Step-by-Step Execution

This dynamic array pipeline executes systematically through structured evaluation stages:

• Data Sanitisation (FILTER): Cleans the input range by discarding empty cells and non-numeric labels, preventing #VALUE! propagation.
• Threshold Calculation (PERCENTILE.INC): Computes numerical cutoff points corresponding to the specified 25th, 50th, 75th, and 90th percentiles.
• Binning (FREQUENCY): Sorts observation counts into discrete bins. Because FREQUENCY outputs n+1 rows for n thresholds, the final bucket captures observations exceeding the 90th percentile.
• Cumulative Aggregation (SCAN): Iterates through proportional share allocations, dynamically calculating the progressive cumulative distribution up to 100%.
• Vertical Matrix Assembly (VSTACK): Stacks the descriptive column headers directly above the data matrix to present a complete, publication-ready summary table.

Formatting and Formatting Customisation

To ensure numerical figures render professionally across reporting presentations:

1. Select the frequency columns and format as integers (#,##0).
2. Select the share and cumulative distribution columns and format as percentages with one decimal place (0.0%).
3. As new rows append to the source data column, the entire distribution updates immediately without requiring macro triggers or pivot cache refreshes.