How to Use VSTACK and LAMBDA to Author Dynamic Mehran Inequality Index Models in Microsoft Excel

Econometric analysis and financial modelling frequently evaluate income and asset distributions using inequality coefficients. While the Gini coefficient remains the industry standard, it places maximum statistical weight upon middle-tier observations. The Mehran inequality index modifies this weighting scheme by placing greater sensitivity upon lower-income quantiles, making it invaluable for poverty assessment and bottom-tier economic equity modelling. Leveraging modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—enables the creation of responsive, reusable Mehran index models without legacy VBA macros.

Mathematical Foundations of the Mehran Index

Given a sorted vector of discrete values $y_1 le y_2 le dots le y_n$ with mean $mu$, the discrete Mehran inequality coefficient $M$ evaluates the cumulative population shares using linear weighting favouring the lower end of the distribution:

M = 1 - (1 / (n^2 * mu)) * SUM((2*n - 2*i + 1) * y_i)

Where:

• n represents the total count of sample units.
• mu represents the arithmetic mean of the distribution.
• i represents the 1-based ascending rank of each observation.
• (2*n - 2*i + 1) functions as the decaying linear weighting factor amplifying bottom observations.

Constructing the Dynamic Array Formula in Excel

Using the LET function, we can structure the mathematical formulation into readable, intermediate calculation variables that operate over any uncurated dataset column:

=LET(
    raw_data, A2:A101,
    clean_data, FILTER(raw_data, ISNUMBER(raw_data) * (raw_data > 0)),
    sorted_data, SORT(clean_data, 1, 1),
    n, COUNT(sorted_data),
    mu, AVERAGE(sorted_data),
    rank_idx, SEQUENCE(n, 1, 1, 1),
    weights, (2 * n - 2 * rank_idx + 1),
    weighted_sum, SUMPRODUCT(weights, sorted_data),
    mehran_index, 1 - (weighted_sum / (n^2 * mu)),
    mehran_index
)

This dynamic formulation automatically filters invalid text entries or empty rows, sorts data into strict ascending order, generates sequential ranks, and computes the exact Mehran coefficient instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this calculation across enterprise workbooks without repeatedly pasting complex formulas, define a reusable custom function named MEHRAN.INDEX within the Excel Name Manager:

1. Navigate to the Formulas tab on the Excel ribbon and click Name Manager.
2. Click New, enter MEHRAN.INDEX in the Name field, and set the Scope to Workbook.
3. In the Refers to box, input the following LAMBDA definition:

   =LAMBDA(values,
       LET(
           clean, FILTER(values, ISNUMBER(values) * (values > 0)),
           sorted, SORT(clean, 1, 1),
           n, COUNT(sorted),
           mu, AVERAGE(sorted),
           i, SEQUENCE(n, 1, 1, 1),
           w, (2 * n - 2 * i + 1),
           1 - (SUM(w * sorted) / (n^2 * mu))
       )
   )

4. Click OK and close the Name Manager.

Building Comparative Distribution Models with VSTACK

With the custom function established, we can combine multiple regional or departmental income cohorts into a single side-by-side comparative table using VSTACK:

=LET(
    cohort_A, B2:B50,
    cohort_B, C2:C50,
    header, HSTACK("Distribution Cohort", "Mehran Index", "Sample Count"),
    row_A, HSTACK("North Region", MEHRAN.INDEX(cohort_A), COUNT(cohort_A)),
    row_B, HSTACK("South Region", MEHRAN.INDEX(cohort_B), COUNT(cohort_B)),
    VSTACK(header, row_A, row_B)
)

This formula dynamically stacks table headers and computational results into a cohesive dashboard block. As upstream data values update or expand, all Mehran inequality scores recalculate instantly, providing robust sensitivity analytics across economic distribution tiers.