How to Use VSTACK and LAMBDA to Author Dynamic Decile Dispersion Ratios (P90/P10 and P90/P50) in Microsoft Excel

In wage distribution analysis and labour market economics, assessing income inequality using aggregate indices like the Gini coefficient can obscure critical structural wage compression or polarization occurring in specific segments of the workforce. Economists at the OECD and the Bureau of Labor Statistics rely on interdecile dispersion ratios—specifically the P90/P10 (overall wage dispersion), P90/P50 (upper-tail inequality), and P50/P10 (lower-tail inequality) ratios. In modern Microsoft Excel, dynamic array formulas combining LAMBDA, LET, PERCENTILE.INC, and VSTACK enable automated, macro-free calculation of these percentile ratios.

Mathematical Foundations of Interdecile Wage Ratios

Interdecile ratios compare specific income or compensation percentiles ((P_k)) where (k) percent of the population earns less than or equal to that cut-off threshold:

• Overall Dispersion Ratio ((P_{90}/P_{10})): [ ext{Ratio}_{90/10} = rac{P_{90}}{P_{10}} ] Measures the wage gap between high-income earners (90th percentile) and low-income earners (10th percentile). An interdecile ratio of 4.0 indicates that high earners receive four times the wage of workers at the bottom decile.
• Upper-Tail Inequality Ratio ((P_{90}/P_{50})): [ ext{Ratio}_{90/50} = rac{P_{90}}{P_{50}} ] Measures wage skewness at the top relative to median compensation, isolating managerial and executive premium growth.
• Lower-Tail Inequality Ratio ((P_{50}/P_{10})): [ ext{Ratio}_{50/10} = rac{P_{50}}{P_{10}} ] Measures how far minimum-wage and entry-level workers lag behind the national median.
• Logarithmic Additivity: In econometric models, the overall ratio decomposes cleanly in log space: [ ln(P_{90}/P_{10}) = ln(P_{90}/P_{50}) + ln(P_{50}/P_{10}) ]

Authoring the Dynamic Interdecile Model in Microsoft Excel

Place your raw employee salary or survey data in column range B2:B50. In cell D2, enter this dynamic array formula:

=LET(
    salaries, B2:B50,
    clean_data, FILTER(salaries, ISNUMBER(salaries) * (salaries > 0)),
    n_count, ROWS(clean_data),
    
    /* Calculate Standard Decile Cutoffs */
    p10, PERCENTILE.INC(clean_data, 0.10),
    p25, PERCENTILE.INC(clean_data, 0.25),
    p50, PERCENTILE.INC(clean_data, 0.50),
    p75, PERCENTILE.INC(clean_data, 0.75),
    p90, PERCENTILE.INC(clean_data, 0.90),
    p95, PERCENTILE.INC(clean_data, 0.95),
    
    /* Calculate Interdecile and Interquartile Ratios */
    r_90_10, p90 / p10,
    r_90_50, p90 / p50,
    r_50_10, p50 / p10,
    r_75_25, p75 / p25,
    r_95_50, p95 / p50,
    
    /* Logarithmic Disparity Components */
    ln_total, LN(r_90_10),
    ln_upper, LN(r_90_50),
    ln_lower, LN(r_50_10),
    pct_upper, ln_upper / ln_total,
    pct_lower, ln_lower / ln_total,
    
    /* Assemble Spilled Report Dashboard */
    headers, HSTACK("Decile Disparity Metric", "Ratio Value", "Log Spread", "Contribution to Overall Dispersion (%)"),
    row1, HSTACK("Overall Dispersion (P90/P10)", TEXT(r_90_10, "0.00"), TEXT(ln_total, "0.000"), "100.0% (Total Dispersion)"),
    row2, HSTACK("Upper-Tail Inequality (P90/P50)", TEXT(r_90_50, "0.00"), TEXT(ln_upper, "0.000"), TEXT(pct_upper, "0.0%")),
    row3, HSTACK("Lower-Tail Inequality (P50/P10)", TEXT(r_50_10, "0.00"), TEXT(ln_lower, "0.000"), TEXT(pct_lower, "0.0%")),
    row4, HSTACK("Interquartile Ratio (P75/P25)", TEXT(r_75_25, "0.00"), TEXT(LN(r_75_25), "0.000"), "Middle 50% Spread"),
    row5, HSTACK("Top 5% Extreme Skew (P95/P50)", TEXT(r_95_50, "0.00"), TEXT(LN(r_95_50), "0.000"), "Executive Premium"),
    
    thresh_head, HSTACK("Percentile Cutoff", "Threshold (£)", "Rank Interpretation", ""),
    t1, HSTACK("10th Percentile (P10)", TEXT(p10, "£#,##0"), "Bottom decile boundary", ""),
    t2, HSTACK("50th Percentile (Median P50)", TEXT(p50, "£#,##0"), "Distribution median midpoint", ""),
    t3, HSTACK("90th Percentile (P90)", TEXT(p90, "£#,##0"), "Top decile entry threshold", ""),
    
    VSTACK(headers, row1, row2, row3, row4, row5, HSTACK("", "", "", ""), thresh_head, t1, t2, t3)
)

Interpreting Output Findings in Corporate Audits

1. Decomposing Disparity Sources: The percentage contribution columns reveal whether overall workforce disparity is primarily driven by upper-tail executive expansion ((P_{90}/P_{50})) or lower-tail minimum wage compression ((P_{50}/P_{10})).
2. Outlier Resilience: Because percentile thresholds evaluate rank order cut-offs rather than arithmetic sums, a single multimillion-pound executive salary will not distort the (P_{90}) threshold, providing a stable baseline.
3. Instant Dynamic Recalibration: Modifying or pasting new payroll records immediately refreshes all percentile cut-offs and logarithmic contributions across the spilled table.