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

In quantitative economic mobility analysis, traditional mobility indices evaluate origin-independence (how far final incomes deviate from initial ranks). However, welfare economists—notably Gary Fields and Efe Ok—demonstrated that origin-independence fails to measure income movement itself. An economy where everyone experiences identical absolute income growth displays zero rank mobility under classical measures, yet substantial economic movement has occurred. The Fields-Ok Mobility Index ($M_{FO}$) solves this by quantifying the average absolute dollar movement across individuals over time. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic Fields mobility models cleanly without legacy VBA macros.

Mathematical Foundations of the Fields-Ok Index

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)$, the discrete absolute Fields-Ok Mobility Index $M_{FO}$ is defined as:

M_FO = (1 / n) * SUM( |y_i - x_i| )

To evaluate proportional movement relative to initial income (satisfying scale invariance):

M_FO_rel = (1 / n) * SUM( |ln(y_i) - ln(x_i)| )

Where:

• n denotes the total population count in the longitudinal panel.
• x_i represents individual initial income.
• y_i represents individual final income.
• |y_i - x_i| represents the absolute distance moved by individual $i$, regardless of direction (both upward growth and downward losses contribute positively to mobility).
• The formulation decomposes cleanly into growth-driven movement and rank-reversal movement.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the Fields-Ok 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),
    abs_movements, ABS(y - x),
    fields_abs_index, AVERAGE(abs_movements),
    rel_movements, ABS(LN(y) - LN(x)),
    fields_rel_index, AVERAGE(rel_movements),
    fields_abs_index
)

This dynamic formula filters synchronized observations, calculates individual absolute deviations, and derives the composite Fields mobility index instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

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

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

   =LAMBDA(initial_range, final_range,
       LET(
           filter_valid, ISNUMBER(initial_range) * ISNUMBER(final_range) * (initial_range > 0) * (final_range > 0),
           x, FILTER(initial_range, filter_valid),
           y, FILTER(final_range, filter_valid),
           AVERAGE(ABS(y - x))
       )
   )

4. Click OK and close the dialogue.

Building Comparative Cohort Dashboards with VSTACK

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

=LET(
    tech_p1, A2:A50, tech_p2, B2:B50,
    mfg_p1, C2:C50, mfg_p2, D2:D50,
    header, HSTACK("Industry Sector", "Fields Absolute Movement (£)", "Sample Count"),
    row_1, HSTACK("Technology Services", FIELDS.MOBILITY(tech_p1, tech_p2), COUNT(tech_p1)),
    row_2, HSTACK("Manufacturing", FIELDS.MOBILITY(mfg_p1, mfg_p2), COUNT(mfg_p1)),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever longitudinal tracking data expands, delivering instantaneous insight into economic movement and income dynamism across different industry divisions.