How to Use VSTACK and LAMBDA to Author Dynamic Sen Poverty Index Models in Microsoft Excel

In development economics and social policy assessment, measuring poverty purely by the headcount ratio (the proportion of people living below a poverty threshold) is fundamentally flawed: it fails to capture either the depth of poverty (how far people fall below the threshold) or the distribution of income among the poor. Nobel laureate Amartya Sen resolved these limitations by formulating the Sen Poverty Index ($S$), which synthesises the headcount ratio, the income gap ratio, and the Gini coefficient among the poor into a single axiomatic measure. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic Sen poverty models cleanly without legacy macros.

Mathematical Foundations of the Sen Index

Given a community distribution with poverty line $z$, let $q$ be the number of individuals whose income falls below $z$, and $n$ be the total population. The discrete Sen Poverty Index $S$ is defined as:

S = H * ( I + (1 - I) * G_p )

Where:

• H = q / n represents the Headcount Ratio (the fraction of the population living in poverty).
• I = (z - mu_p) / z represents the Income Gap Ratio, where mu_p is the mean income of the poor.
• G_p represents the Gini coefficient of income distribution among the poor population alone.
• $S$ ranges from 0 (nobody in poverty) to 1 (everyone has zero income), increasing monotonically when poverty incidence, poverty depth, or inequality among the poor intensifies.

Implementing the Dynamic Array Formula in Excel

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

=LET(
    raw_data, A2:A101,
    z, 1500,
    clean_data, FILTER(raw_data, ISNUMBER(raw_data) * (raw_data > 0)),
    n, COUNT(clean_data),
    poor_data, FILTER(clean_data, clean_data < z),
    q, COUNT(poor_data),
    H, q / n,
    mu_p, AVERAGE(poor_data),
    I, (z - mu_p) / z,
    sorted_poor, SORT(poor_data, 1, 1),
    rank_p, SEQUENCE(q, 1, 1, 1),
    weights, (2 * rank_p - q - 1),
    G_p, (2 / (q^2 * mu_p)) * SUMPRODUCT(weights, sorted_poor),
    sen_index, H * (I + (1 - I) * G_p),
    sen_index
)

This dynamic formula automatically filters observations below the poverty line, computes the headcount ratio and income gap, evaluates the Gini among the poor, and calculates the composite Sen index instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

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

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

   =LAMBDA(values, poverty_line,
       LET(
           clean, FILTER(values, ISNUMBER(values) * (values > 0)),
           n, COUNT(clean),
           poor, FILTER(clean, clean < poverty_line),
           q, COUNT(poor),
           IF(q = 0, 0,
               LET(
                   H, q / n,
                   mu_p, AVERAGE(poor),
                   I, (poverty_line - mu_p) / poverty_line,
                   sorted_p, SORT(poor, 1, 1),
                   i, SEQUENCE(q, 1, 1, 1),
                   w, (2 * i - q - 1),
                   G_p, (2 / (q^2 * mu_p)) * SUM(w * sorted_p),
                   H * (I + (1 - I) * G_p)
               )
           )
       )
   )

4. Click OK and close the dialogue.

Building Comparative Regional Dashboards with VSTACK

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

=LET(
    cohort_A, B2:B100,
    cohort_B, C2:C100,
    line, 1500,
    header, HSTACK("Survey Region", "Sen Poverty Index", "Mean Income (£)", "Sample Count"),
    row_A, HSTACK("Northern District", SEN.INDEX(cohort_A, line), AVERAGE(cohort_A), COUNT(cohort_A)),
    row_B, HSTACK("Southern District", SEN.INDEX(cohort_B, line), AVERAGE(cohort_B), COUNT(cohort_B)),
    VSTACK(header, row_A, row_B)
)

This dynamic array structure automatically updates whenever underlying survey data changes, delivering instantaneous insight into poverty depth and inequality across different demographic segments.