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

Econometric analysis frequently requires complementary inequality indicators to benchmark against the Gini coefficient. While the Mehran index accentuates lower-tier poverty gaps, the Piesch inequality index operates on the opposite end of the spectrum, placing progressive mathematical weighting upon upper-tier income and wealth concentrations. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow financial analysts to model dynamic Piesch concentration curves cleanly without legacy VBA macros.

Mathematical Foundations of the Piesch Index

Given a sorted distribution vector $y_1 le y_2 le dots le y_n$ with mean $mu$, the discrete Piesch inequality index $P$ evaluates cumulative concentration using quadratic weighting that penalises extreme wealth accumulation at the top of the distribution:

P = (3 / (2 * n^2 * mu)) * SUM((2*i - n - 1) * y_i)

Where:

• n denotes the total count of valid observation units.
• mu represents the arithmetic mean of the distribution.
• i represents the 1-based ascending rank index of observation $y_i$.
• (2*i - n - 1) represents the linear rank offset weighting factor.
• The leading scalar factor 3 / 2 normalises the bounded metric between 0 (perfect equality) and 1 (maximum concentration).

Implementing the Piesch Dynamic Array Formula

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 * rank_idx - n - 1),
    weighted_sum, SUMPRODUCT(weights, sorted_data),
    piesch_index, (3 / (2 * n^2 * mu)) * weighted_sum,
    piesch_index
)

This formulation automatically strips invalid non-numeric entries, sorts observations into ascending order, generates dynamic rank sequences, and computes the normalised Piesch coefficient instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this metric across multiple financial models, encapsulate the formulation into a custom function named PIESCH.INDEX within the Excel Name Manager:

1. Go to the Formulas tab and click Name Manager.
2. Click New, enter PIESCH.INDEX in the Name field, and set Scope to Workbook.
3. In the Refers to box, input the following formula:

   =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 * i - n - 1),
           (3 / (2 * n^2 * mu)) * SUM(w * sorted)
       )
   )

4. Click OK and close the dialogue.

Building Multi-Cohort Portfolios with VSTACK

We can combine multiple regional or departmental wealth distributions into a comparative dashboard using VSTACK:

=LET(
    portfolio_A, B2:B100,
    portfolio_B, C2:C100,
    header, HSTACK("Wealth Distribution Cohort", "Piesch Index", "Sample Count"),
    row_A, HSTACK("High Net Worth Segment", PIESCH.INDEX(portfolio_A), COUNT(portfolio_A)),
    row_B, HSTACK("Mass Affluent Segment", PIESCH.INDEX(portfolio_B), COUNT(portfolio_B)),
    VSTACK(header, row_A, row_B)
)

This dynamic array structure automatically updates whenever underlying cohort rows change, delivering instantaneous insight into upper-tier wealth concentrations across different business units or demographic segments.