How to Use VSTACK and LAMBDA to Author Dynamic Decile Ratio Dispersion Models in Microsoft Excel

In labour economics, executive compensation auditing, and income distribution analytics, single-number inequality measures like the Gini coefficient can obscure where in the distribution inequality is widening. A society experiencing wage stagnation at the median alongside surging executive pay produces a very different economic reality than one with a collapsing lower decile. Inter-decile dispersion ratios—specifically the 90/10 ratio (P90/P10), the upper tail ratio (P90/P50), and the lower tail ratio (P50/P10)—isolate inequality at the top versus bottom. By leveraging dynamic array functions—specifically VSTACK, HSTACK, LAMBDA, LET, and PERCENTILE.INC—analysts can construct dynamic dispersion scorecards in Microsoft Excel without legacy VBA macros.

Mathematical Definitions of Inter-Decile Dispersion Ratios

For an income or wage distribution, let P10, P50 (the median), and P90 denote the 10th, 50th, and 90th percentiles respectively. The primary inter-decile metrics are defined as:

• Overall Dispersion (P90/P10): Measures the ratio between high-income earners and low-income earners across the entire distribution.
• Upper Tail Inequality (P90/P50): Measures the premium enjoyed by the top 10% relative to median workers.
• Lower Tail Inequality (P50/P10): Measures the deficit experienced by the bottom 10% relative to median workers.

Notice the identity (P90 / P10) = (P90 / P50) * (P50 / P10). This multiplicative decomposition allows analysts to immediately determine whether rising overall inequality is driven by top-heavy executive compensation or deteriorating entry-level wages.

Authoring the Dynamic Summary LAMBDA Function

In Microsoft Excel (Microsoft 365 or Excel 2024), navigate to Formulas > Name Manager and define a defined name titled DECILE_DISPERSION with the following dynamic formula:

=LAMBDA(data_range,
    LET(
        clean, FILTER(data_range, (data_range > 0) * ISNUMBER(data_range)),
        p10, PERCENTILE.INC(clean, 0.1),
        p50, PERCENTILE.INC(clean, 0.5),
        p90, PERCENTILE.INC(clean, 0.9),
        r90_10, p90 / p10,
        r90_50, p90 / p50,
        r50_10, p50 / p10,
        HSTACK(p10, p50, p90, r90_10, r90_50, r50_10)
    )
)

This LAMBDA handles raw salary numbers dynamically, eliminates blanks, computes exact mathematical percentiles, and resolves the three core dispersion ratios.

Constructing the Full Dynamic Scorecard with VSTACK

Suppose employee annual salaries are listed in cells A2:A25. In cell C2, input this self-contained reporting formula:

=LET(
    salaries, A2:A25,
    metrics, DECILE_DISPERSION(salaries),
    p10_val, INDEX(metrics, 1),
    p50_val, INDEX(metrics, 2),
    p90_val, INDEX(metrics, 3),
    r90_10_val, INDEX(metrics, 4),
    r90_50_val, INDEX(metrics, 5),
    r50_10_val, INDEX(metrics, 6),
    
    header, HSTACK("Dispersion Metric", "Percentile / Ratio", "Analytical Benchmark"),
    r1, HSTACK("10th Percentile (P10)", TEXT(p10_val, "£#,##0"), "Lower threshold earnings"),
    r2, HSTACK("50th Percentile / Median (P50)", TEXT(p50_val, "£#,##0"), "Median workforce benchmark"),
    r3, HSTACK("90th Percentile (P90)", TEXT(p90_val, "£#,##0"), "Upper threshold earnings"),
    r4, HSTACK("Overall Dispersion (P90/P10)", ROUND(r90_10_val, 2) & "x", IF(r90_10_val > 4.0, "High Overall Dispersion", "Compressed")),
    r5, HSTACK("Upper Tail Inequality (P90/P50)", ROUND(r90_50_val, 2) & "x", IF(r90_50_val > 2.2, "Executive Skewed", "Balanced Upper")),
    r6, HSTACK("Lower Tail Inequality (P50/P10)", ROUND(r50_10_val, 2) & "x", IF(r50_10_val > 2.0, "Entry-Level Deficit", "Protected Lower")),
    
    VSTACK(header, r1, r2, r3, r4, r5, r6)
)

Analytical Advantages in Corporate Auditing

1. Pinpointed Inequality Source: Instantly reveals whether widening compensation gaps stem from top-tier bonuses (elevated P90/P50) or minimum wage divergence (elevated P50/P10).
2. Instant Spilled Updates: Adjusting individual salaries dynamically recalculates percentiles and ratio benchmarks with zero manual adjustments.
3. 100% Macro-Free: Operates natively across all modern versions of Excel on Windows, macOS, iPad, and Excel for Web with zero security permission prompts.