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

In quantitative sociology, behavioral economics, and organizational compensation analysis, evaluating pay disparity purely through aggregate metrics (such as the Gini coefficient or variance) overlooks the psychological and social reality of relative deprivation. First conceptualized by Samuel Stouffer and formalized mathematically by Shlomo Yitzhaki and J.K. Ghosh, relative deprivation measures the cumulative economic disadvantage an individual feels when comparing their income against peers who earn more. The Ghosh Relative Deprivation Index formalizes this across the population into an aggregate index. In modern Microsoft Excel, leveraging dynamic array formulas with LAMBDA, SORT, and VSTACK provides an automated modeling engine without macros.

Understanding the Yitzhaki-Ghosh Relative Deprivation Formulation

For an income distribution sorted in ascending order (y_1 le y_2 le dots le y_N) with population mean (mu), the relative deprivation (D_i) experienced by individual (i) is the sum of income gaps between individual (i) and all individuals earning more than (y_i), normalized by (N):

D_i = (1 / N) * SUM( (y_j - y_i) )   [for all y_j > y_i]
D_i = (1 - (i / N)) * ( mu_{>i} - y_i )

• Individual Deprivation ((D_i)): The highest earner ((i = N)) experiences zero deprivation ((D_N = 0)). The lowest earner ((i = 1)) experiences maximum deprivation, measuring the full shortfall between their income and the higher population.
• Aggregate Deprivation Index ((ar{D})): The average deprivation across all individuals in the society is mathematically equivalent to (mu imes G), where (G) is the standard Gini coefficient.
• The Ghosh Relative Deprivation Index ((I_G)): Normalizes aggregate deprivation by average income: (I_G = ar{D} / mu = G). Evaluating the full vector of individual deprivation scores exposes organizational morale risks that aggregate metrics conceal.

Building the Dynamic LAMBDA Deprivation Engine

Assuming raw observations reside in A2:A50 (e.g. Employee Salaries or Departmental Compensation), author a reusable formula using LET:

=LAMBDA(values,
    LET(
        clean_y, FILTER(values, ISNUMBER(values) * (values > 0)),
        n, ROWS(clean_y),
        sorted, SORT(clean_y, 1, 1),
        mu, AVERAGE(sorted),
        ranks, SEQUENCE(n),
        
        // Compute running upper-tail cumulative means
        cum_rev, SCAN(0, SORT(sorted, 1, -1), LAMBDA(acc, x, acc + x)),
        upper_sums, SORT(cum_rev, 1, -1),
        upper_counts, n - ranks,
        
        // Individual deprivation scores
        d_vec, IF(upper_counts = 0, 0, (upper_sums - upper_counts * sorted) / n),
        avg_d, AVERAGE(d_vec),
        ghosh_index, avg_d / mu,
        
        HSTACK(avg_d, ghosh_index)
    )
)

Constructing the Executive Summary Dashboard with VSTACK

Assemble a comprehensive spilled reporting dashboard comparing average deprivation, maximum individual deprivation, and Gini alignment using VSTACK and HSTACK:

=LET(
    incomes, B2:B40,
    clean_d, FILTER(incomes, ISNUMBER(incomes) * (incomes > 0)),
    n_count, ROWS(clean_d),
    s_d, SORT(clean_d, 1, 1),
    mean_val, AVERAGE(s_d),
    r_seq, SEQUENCE(n_count),
    
    // Reverse accumulation for upper sums
    rev_sorted, SORT(s_d, 1, -1),
    c_rev, SCAN(0, rev_sorted, LAMBDA(a, v, a + v)),
    u_sums, SORT(c_rev, 1, -1),
    u_counts, n_count - r_seq,
    
    indiv_depriv, IF(u_counts = 0, 0, (u_sums - u_counts * s_d) / n_count),
    mean_depriv, AVERAGE(indiv_depriv),
    max_depriv, MAX(indiv_depriv),
    ghosh_val, mean_depriv / mean_val,
    
    headers, HSTACK("Deprivation Metric", "Calculated Value", "Organizational Risk"),
    row1, HSTACK("Aggregate Deprivation Score", TEXT(mean_depriv, "$#,##0.00"), "Mean Perceived Compensation Gap"),
    row2, HSTACK("Maximum Individual Deprivation", TEXT(max_depriv, "$#,##0.00"), "Severe Risk in Lowest-Paid Tier"),
    row3, HSTACK("Ghosh Relative Index (D_bar / mu)", TEXT(ghosh_val, "0.0000"), IF(ghosh_val > 0.4, "High Structural Deprivation", "Moderate Disparity Tension")),
    row4, HSTACK("Population Benchmark Mean (mu)", TEXT(mean_val, "$#,##0.00"), "Baseline Production Average"),
    
    VSTACK(headers, row1, row2, row3, row4)
)

Key Advantages of the Implementation

1. Instant Dynamic Recalculation: Modifying source compensation numbers immediately updates individual deprivation scores, aggregate deprivation, and the Ghosh index across adjacent cells.
2. Self-Contained Sorting: The formula sorts source data internally via SORT(), guaranteeing mathematical precision without disturbing the source worksheet order.
3. Macro-Free Portability: Encapsulate the formula inside Excel’s Name Manager as CALC_GHOSH_DEPRIVATION for seamless re-use across executive auditing and enterprise compensation models.