How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Centile Rank Mobility Models in Microsoft Excel

In quantitative economic mobility research, while transition matrices frequently evaluate coarse quintiles or deciles (5 to 10 buckets), fine-grained policy analysis requires percentile-level granularity: evaluating the probability that an individual born in the 10th percentile reaches the 90th percentile as an adult. Anthony Shorrocks pioneered rank mobility frameworks that model movement across normalized centile ranks. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic centile rank mobility models cleanly without legacy macros.

Mathematical Foundations of Centile Rank Mobility

Given a longitudinal panel of $n$ individuals observed across initial period incomes $x = (x_1, dots, x_n)$ and final period incomes $y = (y_1, dots, y_n)$, let $R_x(i) in [0, 1]$ and $R_y(i) in [0, 1]$ represent the normalized fractional ranks:

R_x(i) = rank(x_i) / n
R_y(i) = rank(y_i) / n

The Rank Mobility Correlation Coefficient ($ ho_{rank}$) and Mean Absolute Centile Shift ($Delta_{centile}$) evaluate the magnitude of rank reordering:

Delta_centile = (1 / n) * SUM( |R_y(i) - R_x(i)| ) * 100

Where:

• Delta_centile represents the average number of percentiles an individual moves across the distribution over the longitudinal window.
• If no mobility exists (individuals maintain identical relative ranks), $Delta_{centile} = 0$.
• Under complete random mobility (origin rank is independent of destination rank), the theoretical expected shift is approximately $33.3$ percentiles.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the centile rank mobility calculation dynamically across two synchronized income columns:

=LET(
    initial_col, A2:A101,
    final_col, B2:B101,
    clean_filter, ISNUMBER(initial_col) * ISNUMBER(final_col) * (initial_col > 0) * (final_col > 0),
    x, FILTER(initial_col, clean_filter),
    y, FILTER(final_col, clean_filter),
    n, COUNT(x),
    
    // Normalized centile ranks
    rank_x, RANK.AVG(x, x, 1) / n,
    rank_y, RANK.AVG(y, y, 1) / n,
    
    // Absolute centile shifts
    centile_shifts, ABS(rank_y - rank_x) * 100,
    mean_shift, AVERAGE(centile_shifts),
    
    // Spearman rank correlation
    rank_correl, CORREL(rank_x, rank_y),
    rank_mobility_index, 1 - rank_correl,
    
    mean_shift
)

This dynamic formula generates fractional ranks, calculates average percentile movements, computes the Spearman rank correlation, and outputs the centile shift metric instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this metric across multiple economic workbooks, define a reusable custom function named CENTILE.MOBILITY in the Excel Name Manager:

1. Navigate to the Formulas tab and click Name Manager.
2. Click New, enter CENTILE.MOBILITY in the Name field, and set Scope to Workbook.
3. In the Refers to box, input the formula to calculate mean centile shifts.
4. Click OK and close the dialogue.

Building Comparative Centile Dashboards with VSTACK

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

=LET(
    urban_p1, A2:A100, urban_p2, B2:B100,
    rural_p1, C2:C100, rural_p2, D2:D100,
    header, HSTACK("Demographic Panel", "Mean Centile Shift", "Rank Correlation", "Mobility Assessment"),
    row_1, HSTACK("Metropolitan Innovation Cluster", CENTILE.MOBILITY(urban_p1, urban_p2), 0.42, "High Percentile Turnover"),
    row_2, HSTACK("Agricultural Basin", CENTILE.MOBILITY(rural_p1, rural_p2), 0.78, "Strong Rank Persistence"),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever longitudinal tracking data expands, delivering instantaneous insight into percentile churn and rank mobility across socioeconomic cohorts.