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

While the standard Gini coefficient measures relative economic inequality by dividing mean absolute differences by the overall distribution mean, it satisfies scale invariance: doubling all wages leaves relative inequality unchanged. In contrast, welfare economists and wage equity analysts frequently require the Absolute Gini index (also known as the Gini Mean Difference or translation-invariant Gini), which measures dispersion in raw currency terms. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow analysts to build dynamic Absolute Gini index models cleanly without legacy macros.

Mathematical Foundations of the Absolute Gini Index

Given a sorted distribution vector $y_1 le y_2 le dots le y_n$, the discrete Absolute Gini coefficient $A$ evaluates the mean difference across all pairwise comparisons in the original units of measurement:

A = (2 / (n^2)) * SUM((2*i - n - 1) * y_i)

Where:

• n denotes the total count of valid observation units.
• i represents the 1-based ascending rank of observation $y_i$.
• (2*i - n - 1) represents the linear rank offset weighting factor.
• Unlike the relative Gini coefficient, the Absolute Gini does not divide by the arithmetic mean $mu$, expressing inequality in tangible currency units (such as Pounds or Dollars).

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can structure the mathematical calculation into readable intermediate steps that handle uncurated columns dynamically:

=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),
    rank_idx, SEQUENCE(n, 1, 1, 1),
    weights, (2 * rank_idx - n - 1),
    weighted_sum, SUMPRODUCT(weights, sorted_data),
    abs_gini, (2 / (n^2)) * weighted_sum,
    abs_gini
)

This formula strips invalid text entries, sorts observations into strict ascending order, computes dynamic rank sequences, and outputs the exact Absolute Gini disparity metric in real time.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this metric across multiple financial models, define a reusable custom function named ABSOLUTE.GINI in the Excel Name Manager:

1. Navigate to the Formulas tab and click Name Manager.
2. Click New, enter ABSOLUTE.GINI 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),
           i, SEQUENCE(n, 1, 1, 1),
           w, (2 * i - n - 1),
           (2 / (n^2)) * SUM(w * sorted)
       )
   )

4. Click OK and close the dialogue.

Building Comparative Distribution Models with VSTACK

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

=LET(
    cohort_A, B2:B100,
    cohort_B, C2:C100,
    header, HSTACK("Department Cohort", "Absolute Gini (£)", "Relative Gini", "Mean (£)"),
    row_A, HSTACK("Engineering", ABSOLUTE.GINI(cohort_A), ABSOLUTE.GINI(cohort_A) / (2 * AVERAGE(cohort_A)), AVERAGE(cohort_A)),
    row_B, HSTACK("Operations", ABSOLUTE.GINI(cohort_B), ABSOLUTE.GINI(cohort_B) / (2 * AVERAGE(cohort_B)), AVERAGE(cohort_B)),
    VSTACK(header, row_A, row_B)
)

This dynamic array structure automatically updates whenever underlying cohort rows change, delivering instantaneous insight into absolute wage dispersion alongside traditional relative measures across different business divisions.