How to Use VSTACK and LAMBDA to Author Dynamic Bartholomew Mobility Index Models in Microsoft Excel

In quantitative socioeconomic analysis, transition probability matrices (Markov matrices) model how individuals transition between discrete income quintiles, credit rating buckets, or occupational tiers across successive time periods. David Bartholomew formulated the Bartholomew Mobility Index ($B$), which quantifies overall structural mobility by evaluating the average number of class boundaries crossed per individual. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic Bartholomew transition matrix models cleanly without legacy VBA macros.

Mathematical Foundations of the Bartholomew Index

Given a $K imes K$ transition probability matrix $P$, where $p_{ij}$ represents the probability that an individual in origin class $i$ transitions to destination class $j$, and $w_i$ represents the initial population proportion in class $i$, the discrete Bartholomew Mobility Index $B$ is defined as:

B = SUM( w_i * SUM( p_ij * |i - j| ) )

To normalize the index between 0 and 1 across $K$ ordered classes:

B_norm = B / (K - 1)

Where:

• K denotes the total count of discrete socioeconomic classes or quintiles.
• |i - j| represents the absolute distance (number of category boundaries) traversed during transition.
• If no mobility exists (the matrix is the identity matrix $I$), all transitions satisfy $i = j$, yielding $B = 0$.
• If maximum mobility exists (everyone transitions between extreme opposite tiers), $B_{norm} o 1$.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the normalized Bartholomew calculation dynamically from a $5 imes 5$ quintile transition matrix:

=LET(
    trans_matrix, A2:E6,
    weights, {0.2; 0.2; 0.2; 0.2; 0.2},
    k, ROWS(trans_matrix),
    row_idx, SEQUENCE(k, 1, 1, 1),
    col_idx, SEQUENCE(1, k, 1, 1),
    dist_matrix, ABS(row_idx - col_idx),
    weighted_trans, trans_matrix * dist_matrix,
    class_moves, MMULT(weighted_trans, SEQUENCE(k, 1, 1, 0)),
    bartholomew_raw, SUMPRODUCT(weights, class_moves),
    bartholomew_norm, bartholomew_raw / (k - 1),
    bartholomew_norm
)

This dynamic formula generates distance matrices, evaluates matrix products using MMULT, and outputs the normalized Bartholomew mobility index instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this metric across multiple transition models, define a reusable custom function named BARTHOLOMEW.MOBILITY in the Excel Name Manager:

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

   =LAMBDA(matrix,
       LET(
           k, ROWS(matrix),
           w, SEQUENCE(k, 1, 1/k, 0),
           r, SEQUENCE(k, 1, 1, 1),
           c, SEQUENCE(1, k, 1, 1),
           d, ABS(r - c),
           m, matrix * d,
           SUM(w * MMULT(m, SEQUENCE(k, 1, 1, 0))) / (k - 1)
       )
   )

4. Click OK and close the dialogue.

Building Comparative Transition Dashboards with VSTACK

We can combine multiple regional or generational transition matrices into a side-by-side comparative dashboard using VSTACK:

=LET(
    matrix_US, A2:E6,
    matrix_UK, G2:K6,
    header, HSTACK("Country Transition Panel", "Bartholomew Index", "Quintiles (K)", "Mobility Assessment"),
    row_1, HSTACK("United States Quintiles", BARTHOLOMEW.MOBILITY(matrix_US), 5, "Moderate Inter-Quintile Mobility"),
    row_2, HSTACK("United Kingdom Quintiles", BARTHOLOMEW.MOBILITY(matrix_UK), 5, "High Boundary Fluidity"),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever underlying transition probability estimates change, delivering instantaneous insight into category movement and economic class persistence across international cohorts.