How to Use VSTACK and LAMBDA to Author Dynamic Zhang-Kanbur Polarization Index Models in Microsoft Excel

When analyzing socioeconomic disparities across geographical provinces, urban-rural divides, or demographic cohorts, standard inequality decomposition often obscures whether disparity is driven by internal group variance or structural polarization between groups. Xiaobo Zhang and Ravi Kanbur (2001) developed the Zhang-Kanbur Polarization Index ((P_{ZK})) to explicitly measure the ratio of between-group inequality to within-group inequality. In modern Microsoft Excel, dynamic array formulas combining LAMBDA, LET, BYCOL, and VSTACK enable economists to author automated Zhang-Kanbur polarization models without VBA macros.

Mathematical Foundations of the Zhang-Kanbur Polarization Metric

The Zhang-Kanbur polarization index is defined as the ratio of between-group inequality ((I_B)) to within-group inequality ((I_W)) using the Theil L index (Mean Log Deviation, (GE(0))):

[ P_{ZK} = rac{I_B}{I_W} ]

Where the components decompose cleanly from total Theil L inequality (I = I_B + I_W):

• Between-Group Inequality ((I_B)): [ I_B = sum_{j=1}^K p_j lnleft( rac{mu}{mu_j} ight) ] where (p_j = n_j / N) is the population share of group (j), (mu) is the total population mean income, and (mu_j) is the mean income of group (j).
• Within-Group Inequality ((I_W)): [ I_W = sum_{j=1}^K p_j I_j ] where (I_j) is the internal Theil L inequality within subpopulation (j).
• Axiomatic Property: If groups become internally more homogeneous (reducing (I_W)) while the gulf between group means widens (increasing (I_B)), the ratio (P_{ZK}) surges, signaling elevated societal polarization and conflict potential.

Authoring the Dynamic Zhang-Kanbur Model in Microsoft Excel

Suppose you have survey microdata across two cohorts (Urban and Rural) in columns A and B:

• A2:A41: Cohort Identifier (“Urban” vs “Rural”)
• B2:B41: Household Income (£)

In cell D2, enter this dynamic array formula:

=LET(
    groups, A2:A41,
    incomes, B2:B41,
    clean_filter, ISNUMBER(incomes) * (groups <> ""),
    c_groups, FILTER(groups, clean_filter),
    c_inc, FILTER(incomes, clean_filter),
    
    n_total, ROWS(c_inc),
    mu_total, AVERAGE(c_inc),
    
    /* Helper LAMBDA to compute Theil L (GE0) for any income vector */
    calc_theil_l, LAMBDA(vec,
        LET(
            n, ROWS(vec),
            mu, AVERAGE(vec),
            (1 / n) * SUM(LN(mu / vec))
        )
    ),
    
    /* Total Overall Theil L Inequality */
    theil_total, calc_theil_l(c_inc),
    
    /* Cohort 1: Urban */
    inc_u, FILTER(c_inc, c_groups = "Urban"),
    n_u, ROWS(inc_u),
    mu_u, AVERAGE(inc_u),
    p_u, n_u / n_total,
    theil_u, calc_theil_l(inc_u),
    
    /* Cohort 2: Rural */
    inc_r, FILTER(c_inc, c_groups = "Rural"),
    n_r, ROWS(inc_r),
    mu_r, AVERAGE(inc_r),
    p_r, n_r / n_total,
    theil_r, calc_theil_l(inc_r),
    
    /* Within-Group Inequality: I_W = p_u*I_u + p_r*I_r */
    theil_within, (p_u * theil_u) + (p_r * theil_r),
    
    /* Between-Group Inequality: I_B = p_u*ln(mu/mu_u) + p_r*ln(mu/mu_r) */
    theil_between, (p_u * LN(mu_total / mu_u)) + (p_r * LN(mu_total / mu_r)),
    
    /* Zhang-Kanbur Polarization Index: P_ZK = I_B / I_W */
    p_zk, theil_between / theil_within,
    
    /* Assemble Spilled Report Dashboard */
    headers, HSTACK("Decomposition Component", "Metric Score", "Econometric Interpretation"),
    r1, HSTACK("Total Theil L Inequality (I)", TEXT(theil_total, "0.0000"), "Aggregate mean log deviation"),
    r2, HSTACK("Within-Group Inequality (I_W)", TEXT(theil_within, "0.0000"), "Inequality existing inside cohorts"),
    r3, HSTACK("Between-Group Inequality (I_B)", TEXT(theil_between, "0.0000"), "Inequality driven by group mean disparity"),
    r4, HSTACK("Zhang-Kanbur Polarization (P_ZK)", TEXT(p_zk, "0.0000"), "Ratio of Between-Group to Within-Group Inequality (I_B / I_W)"),
    r5, HSTACK("Between-Group Share (%)", TEXT(theil_between / theil_total, "0.0%"), "Proportion of total inequality attributable to group divide"),
    
    sub_head, HSTACK("Subgroup Details", "Count (N)", "Mean Income (£)", "Internal Theil L"),
    s1, HSTACK("Urban Cohort", TEXT(n_u, "#,##0"), TEXT(mu_u, "£#,##0"), TEXT(theil_u, "0.0000")),
    s2, HSTACK("Rural Cohort", TEXT(n_r, "#,##0"), TEXT(mu_r, "£#,##0"), TEXT(theil_r, "0.0000")),
    
    VSTACK(headers, r1, r2, r3, r4, r5, HSTACK("", "", ""), sub_head, s1, s2)
)

Interpreting Output Findings in Regional Economic Studies

1. Polarization Surge Dynamics: If regional industrial policies lift urban incomes while rural earnings stagnate, (I_B) expands rapidly while (I_W) stays constant, driving up the Zhang-Kanbur index and signaling rising geographical alienation.
2. Exact Theil Decomposition: Because Theil L is strictly additively decomposable, (I_B + I_W) identically matches total inequality, guaranteeing mathematical consistency across audit models.
3. Instant Dynamic Simulation: Modifying or pasting new regional survey numbers immediately recalibrates group means, internal Theil scores, and the composite polarization ratio across the spilled table.