How to Use VSTACK and LAMBDA to Author Dynamic Gini Relative Deprivation Index Models in Microsoft Excel

In welfare economics, sociological modeling, and human resources compensation benchmarking, inequality is not merely an aggregate statistical dispersion. It is experienced individually as relative deprivation—the emotional or financial deficit an individual experiences when comparing their income to peers earning more within the reference group. Economists Shlomo Yitzhaki and W.G. Runciman proved that aggregate relative deprivation across an entire population is mathematically identical to the Gini coefficient multiplied by the mean income. This tutorial shows how to construct dynamic Relative Deprivation models in Microsoft Excel using VSTACK, LAMBDA, LET, and MAP.

Mathematical Formulation of Relative Deprivation

For a population of N individuals sorted in ascending order of income y₁ ≤ y₂ ≤ … ≤ y_N, the relative deprivation RD(y_i) felt by an individual with income y_i is defined as:

RD(y_i) = (1 / N) * ∑_{j = i+1}^N (y_j - y_i)

The top earner y_N experiences zero relative deprivation (RD = 0). The lowest earner experiences maximum deprivation. The total average relative deprivation across the population is:

RD_total = (1 / N) * ∑_{i = 1}^N RD(y_i) = μ * G

Where μ is the mean income and G is the Gini coefficient. This identity provides an elegant mathematical benchmark to audit corporate compensation structures.

Creating the Reusable LAMBDA Function

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

=LAMBDA(incomes,
    LET(
        clean, FILTER(incomes, (incomes >= 0) * ISNUMBER(incomes)),
        sorted, SORT(clean, 1, 1),
        n, ROWS(sorted),
        rd_array, MAP(sorted, LAMBDA(val,
            LET(
                higher_cohort, FILTER(sorted, sorted > val, 0),
                IF(ISARRAY(higher_cohort),
                   SUM(higher_cohort - val) / n,
                   0
                )
            )
        )),
        HSTACK(sorted, rd_array)
    )
)

Constructing the Full Dynamic Scorecard with VSTACK

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

=LET(
    salaries, A2:A16,
    clean, FILTER(salaries, (salaries >= 0) * ISNUMBER(salaries)),
    sorted, SORT(clean, 1, 1),
    n, ROWS(sorted),
    mean_val, AVERAGE(sorted),
    
    rd_matrix, RELATIVE_DEPRIVATION(salaries),
    rd_scores, CHOOSECOLS(rd_matrix, 2),
    avg_rd, AVERAGE(rd_scores),
    
    // Gini calculation via relative deprivation identity
    gini_equiv, avg_rd / mean_val,
    
    header, HSTACK("Compensation Cohort", "Individual Income", "Relative Deprivation Score", "Deprivation Ratio"),
    rows_data, MAKEARRAY(n, 4, LAMBDA(r, c,
        LET(
            inc, INDEX(sorted, r),
            rd, INDEX(rd_scores, r),
            ratio, rd / inc,
            SWITCH(c,
                1, "Employee " & r,
                2, TEXT(inc, "£#,##0"),
                3, TEXT(rd, "£#,##0"),
                4, TEXT(ratio, "0.0%")
            )
        )
    )),
    
    divider, HSTACK("---", "---", "---", "---"),
    summary_mean, HSTACK("Summary Metric", "Mean Income: " & TEXT(mean_val, "£#,##0"), "Average Deprivation: " & TEXT(avg_rd, "£#,##0"), "Implied Gini: " & ROUND(gini_equiv, 4)),
    
    VSTACK(header, rows_data, divider, summary_mean)
)

Strategic Applications in Compensation Analytics

1. Internal Equity Auditing: Identifies individual positions where the deprivation ratio spikes disproportionately relative to peer cohorts, warning management of turnover risks.
2. Instant Dynamic Responding: Adjusting individual salaries dynamically recalculates cohort comparisons, aggregate deprivation, and the implied Gini coefficient without manual spreadsheet adjustments.
3. No-VBA Architecture: Completely portable across Excel for Windows, Mac, iPad, and Excel Online with zero macro permission warnings.